SOLVED

basic command button Yes/No VBA code add text

%3CLINGO-SUB%20id%3D%22lingo-sub-2378556%22%20slang%3D%22en-US%22%3Ebasic%20command%20button%20Yes%2FNo%20VBA%20code%20add%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2378556%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20excel%20community%2C%3C%2FP%3E%3CP%3EI%20am%20good%20with%20excel%20forumla's%20but%20I'm%20a%20beginner%20(less%20than)%20when%20it%20comes%20to%20VBA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20create%202%20buttons.%20Yes%20and%20No%20(I%20can%20do%20this)%3C%2FP%3E%3CP%3EClickable%2C%20to%20input%2C%20%22Yes%22%20or%20%22No%22%20into%20a%20column.%3C%2FP%3E%3CP%3EI%20have%20been%20able%20to%20create%20the%20button%20and%20add%20to%20a%20single%20cell.%3C%2FP%3E%3CP%3EAND%20I%20have%20been%20able%20to%20create%20a%20button%20to%20move%20down%201%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20can't%20create%20a%20button%2C%20to%20input%20YES%20into%20a%20cell%2C%20then%20move%20down%2C%20click%20it%20again%20to%20put%20another%20YES%20OR%20click%20the%20NO%20and%20it%20will%20go%20into%20the%20cell%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EClick%20-%20Yes%3C%2FP%3E%3CP%3EClick%20-%20No%3C%2FP%3E%3CP%3EClick%20-%20Yes%3C%2FP%3E%3CP%3EClick%20-%20Yes.%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20can%20someone%20advise%20code%20for%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2378556%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2378599%22%20slang%3D%22en-US%22%3ERe%3A%20basic%20command%20button%20Yes%2FNo%20VBA%20code%20add%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2378599%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3B%20Thank%20You%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2378624%22%20slang%3D%22en-US%22%3ERe%3A%20basic%20command%20button%20Yes%2FNo%20VBA%20code%20add%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2378624%22%20slang%3D%22en-US%22%3EGlad%20to%20help%20you%2C%2C%2C%20if%20it%20works%20for%20you%20the%20you%20may%20accept%20is%20as%20Best%20Answer%20as%20well%20Like%20too%2C%2C%20and%20keep%20asking%20%E2%98%BA%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2378585%22%20slang%3D%22en-US%22%3ERe%3A%20basic%20command%20button%20Yes%2FNo%20VBA%20code%20add%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2378585%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1060899%22%20target%3D%22_blank%22%3E%40mightymurph%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EThis%20is%20the%20VBA%20code%20you%20can%20use%20for%20Yes%20%26amp%3B%20No.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20CommandButton1_Click()%0A%0ADim%20lastrow%20As%20Long%0Alastrow%20%3D%20ActiveSheet.Cells(Rows.Count%2C%20%22A%22).End(xlUp).Row%20%2B%201%0A%0AIf%20CommandButton1.Caption%20%3D%20%22Yes%22%20Then%0AActiveSheet.Cells(lastrow%2C%20%22A%22).Value%20%3D%20%22Yes%22%0ACommandButton1.Caption%20%3D%20%22No%22%0A%0AElseIf%20CommandButton1.Caption%20%3D%20%22No%22%20Then%0AActiveSheet.Cells(lastrow%2C%20%22A%22).Value%20%3D%20%22No%22%0ACommandButton1.Caption%20%3D%20%22Yes%22%0AEnd%20If%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EAfter%20you%20Create%20Command%20Button%2C%20set%20it's%20CAPTION%20%3CSTRONG%3EYes%3C%2FSTRONG%3E%20(select%20button%20%26amp%3B%20Right%20Click%20then%20Hit%20Property%20).%3C%2FLI%3E%3CLI%3ERemember%20in%20Code%20I've%20use%20CAPTION%20as%20%3CSTRONG%3EYes%3C%2FSTRONG%3E%20%26amp%3B%20%3CSTRONG%3ENo%3C%2FSTRONG%3E%20and%20it's%20case%20sensitive.%3C%2FLI%3E%3CLI%3EBoth%20Column%20name%20as%20well%20Value%20to%20enter%20are%20editable.%3C%2FLI%3E%3CLI%3ESave%20the%20WB%20as%20Macro%20Enabled%20*.xlsm.%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

3 Replies
best response confirmed by allyreckerman (Microsoft)
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.
Glad to help you,,, if it works for you the you may accept is as Best Answer as well Like too,, and keep asking ☺