Hide/Unhide Command button depending on cell value

[roderick50]

Hi all,

I am stuck for days with the following problem. I want to conditionally hide/unhide a command button, depending on a cell's (text) value.

The macro should automatically run on sheet 7, which is called "Front".

The command button that should conditionally hide/unhide is called "Button 1".

The button should hide if cell "D26" = No and unhide if this cell is = Yes.

Thanks in advance for your help!!

 

[Jerry Sullivan]

Hi and Welcome to the Board,

"Button 1" is probably a Form Control. If you use an ActiveX control you might find it easier to control its properties.
You can insert an ActiveX button from the same Toolbox you used to insert your Form Button.

Here is some code that will work with an ActiveX button...

Private Sub Worksheet_Change(ByVal Target As Range)
'If Cell value = "Yes" Show Button
    Dim sAddress As String
    sAddress = "$D$26"
    
    With ActiveSheet
        If Intersect(Target, Range(sAddress)) _
            Is Nothing Then Exit Sub
        Debug.Print Range(sAddress).Value
        On Error GoTo CleanUp
        Application.EnableEvents = False
        Me.CommandButton1.Visible = _
            (Range(sAddress).Value = "Yes")
    End With
       
CleanUp:
    Application.EnableEvents = True
End Sub

 

[roderick50]

Thanks for your quick reply!

I have inserted the ActiveX command button as you suggested and I have copied the code in the developer window: VBAProject > Microsoft Excel Objects > Sheet7(Front). However, the button does not hide/unhide based on the value of cell D26.

Do you have any idea what I could be doing wrong? I have made sure that ActiveX controls are activated.

By the way, the command button needs to iniate a macro, which was already working with the Form control button. Could you please advise how to make this work with the ActiveX Control button?

Thanks!

 

[Jerry Sullivan]

It's hard to know what the problem might be without a bit of testing.

One possibility is that you don't have EnableEvents set to True, or possibly macros are not enabled for your workbook?

Try replacing the previous code with this.
It should pop up a message box when you change any cell in the worksheet.
Based on what happens it will help us focus on potential causes.

Private Sub Worksheet_Change(ByVal Target As Range)
'---Shows button if Cell value = "Yes" if not hide button
    Dim sAddress As String
    sAddress = "$D$26"
    MsgBox "Changed range is: & Target.Address "
    With ActiveSheet
        If Intersect(Target, Range(sAddress)) _
            Is Nothing Then Exit Sub
        On Error GoTo CleanUp
        Application.EnableEvents = False
        Me.CommandButton1.Visible = _
            (Range(sAddress).Value = "Yes")
    End With
CleanUp:
    Application.EnableEvents = True
End Sub

By the way, the command button needs to iniate a macro, which was already working with the Form control button. Could you please advise how to make this work with the ActiveX Control button?

First go Design Mode, just like you did when Inserting the Button.
Then double-click on your button.

It will open up the VBA editor in the Sheet Code Module and start out a Procedure like this...

Private Sub CommandButton1_Click()

End Sub

You can call your macro from here. Whenever the button is clicked, the code inside this procedure will be run.

Private Sub CommandButton1_Click()
    Call MyMacroName
End Sub

 

[Jerry Sullivan]

One other thing to check...

The code will do a Case-Sensitive comparison, so make sure the value in Cell D26 is "Yes" and not "YES" or "yes".

We can modify the code if you need it to be Case-Insensitive