Forum Discussion

mightymurph's avatar
mightymurph
Copper Contributor
May 23, 2021
Solved

basic command button Yes/No VBA code add text

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

 

excel
Macros and VBA
  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    May 23, 2021

    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 Replies

Sort By
  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    May 23, 2021

    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.

Resources