Make ActiveX Command Button Visible or Invisible with Dropdown Option ("Yes or No")

[wcm69]

I'm Hoping anybody out there in the Excel World can help me out!

I would like to have the ActiveX Command Button appear (i.e. become visible) when a particular option is chosen (i.e. criteria is met). Until then, I would like the button to be invisible.

In other words - When "yes" is entered (in Cell N2) - via a ("Yes or No") drop down list (I've already created) I would like the ActiveX Command Button to appear in Cell O2.

I've already created the button and tried writing the VBA code for it copying variations of previous codes taken from the internet - without success. Not being particularly proficient in VBA I'm hoping someone can provide the necessary directions and codes that I can copy and paste into my worksheet/module.

My thanks in advance for any help.

 

[dangelor]

A little macro recording and I came up with this. Place in the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("N6") = "Yes" Then
        ActiveSheet.Shapes.Range(Array("CommandButton1")).Visible = True
    Else
        ActiveSheet.Shapes.Range(Array("CommandButton1")).Visible = False
    End If
End Sub

 

[Peter_SSs]

1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Edit the name of the command button in the code (blue) to match your button name.
4. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("N2")) Is Nothing Then Me.CommandButton1.Visible = LCase(Range("N2").Value) = "yes"
End Sub

 

[wcm69]

Hi Peter

Works a treat. Thank you.

I was wondering - If with a little tweaking can the code be fixed run more than one command button?

I've got data in an Excel Table, so as I fill it in and tab to the end, the next row comes up for the next data entry.

It would be perfect if as I complete (data in) each row the command button is available to be made visible or Invisible as in your previous code.

I have 49 rows (after the header) - Ideally I would like to make the command button's function available for each of the 49 rows (2-50) in column O (Cells O2:O50).

If it's not too much of a pain can I you help me out? Always grateful for any advice.

Thanks in advance.

 

[Peter_SSs]

1. So you have 49 command buttons?

If so, ..
2. Are they named CommandButton1, CommandButton2, ..., CommandButton49?

3. And CommandButton1 is linked to cell N2, CommandButton2 is linked to N3 etc?

 

[wcm69]

Thanks Peter

Again with your assistance I've now got it to work for all 49 rows.

just need to work out how to link those command button to Sheet 2 and to open specific hyperlinks?? Any tips gladly welcomed and appreciated.

Wish me luck

Thanks

 

[Peter_SSs]

just need to work out how to link those command button to Sheet 2 and to open specific hyperlinks??

Not being familiar with your workbook or requirements, that mean nothing to me.