Results 1 to 5 of 5

Hide/Unhide Command button depending on cell value

This is a discussion on Hide/Unhide Command button depending on cell value within the Excel Questions forums, part of the Question Forums category; Hi all, I am stuck for days with the following problem. I want to conditionally hide/unhide a command button, depending ...

  1. #1
    New Member
    Join Date
    Dec 2011
    Location
    The Netherlands
    Posts
    2

    Default Hide/Unhide Command button depending on cell value

    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!!

  2. #2
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,881

    Default Re: Hide/Unhide Command button depending on cell value

    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...

    Code:
    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
    Using Excel 2013

  3. #3
    New Member
    Join Date
    Dec 2011
    Location
    The Netherlands
    Posts
    2

    Default Re: Hide/Unhide Command button depending on cell value

    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!

  4. #4
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,881

    Default Re: Hide/Unhide Command button depending on cell value

    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.

    Code:
    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
    Quote Originally Posted by roderick50 View Post
    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...

    Code:
    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.
    Code:
    Private Sub CommandButton1_Click()
        Call MyMacroName
    End Sub
    Using Excel 2013

  5. #5
    MrExcel MVP Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    6,881

    Default Re: Hide/Unhide Command button depending on cell value

    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
    Using Excel 2013

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com