basic command button Yes/No VBA code add text

[mightymurph]

Hello excel community,

I am good with excel forumla's but I'm a beginner (less than) when it comes to VBA.

I want to create 2 buttons. Yes and No (I can do this)

Clickable, to input, "Yes" or "No" into a column.

I have been able to create the button and add to a single cell.

AND I have been able to create a button to move down 1 cell.

But I can't create a button, to input YES into a cell, then move down, click it again to put another YES OR click the NO and it will go into the cell below.

Click - Yes

Click - No

Click - Yes

Click - Yes. and so on.

Please can someone advise code for this.

Thank you

[#2 · Accepted solution]

@mightymurph 

This is the VBA code you can use for Yes & No.

Private Sub CommandButton1_Click()

Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

If CommandButton1.Caption = "Yes" Then
ActiveSheet.Cells(lastrow, "A").Value = "Yes"
CommandButton1.Caption = "No"

ElseIf CommandButton1.Caption = "No" Then
ActiveSheet.Cells(lastrow, "A").Value = "No"
CommandButton1.Caption = "Yes"
End If

End Sub

N.B. 

• After you Create Command Button, set it's CAPTION Yes (select button & Right Click then Hit Property ).
• Remember in Code I've use CAPTION as Yes & No and it's case sensitive.
• Both Column name as well Value to enter are editable.
• Save the WB as Macro Enabled *.xlsm.

[#3]

@Rajesh_Sinha  Thank You 

[#4]

Glad to help you,,, if it works for you the you may accept is as Best Answer as well Like too,, and keep asking ☺