Results 1 to 7 of 7

Thread: Hiding Button based on multiple conditions
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Hiding Button based on multiple conditions

    Hello friends,

    I created an ActiveX button in excel and want it to hide or unhide depending on some conditions,

    I checked another thread on this forum where they used the following code:


    Private Sub Worksheet_Change(ByVal Target As Range) Dim sAddress As String
    sAddress = "D8"

    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.CheckBox1.Visible = _
    (Range(sAddress).Value = "Yes")
    End With

    CleanUp:
    Application.EnableEvents = True

    End Sub




    But as it shows, the button will show only if the cell text value is "Yes"

    What should i do if i want the button to appear if the cell text value is equal to "Yes" or "True" or "Correct"...

    In other words, i want to define multiple texts that will make the button appear.


    Thank you in advance guys
    You would save my life if i get the answer

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,873
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Hiding Button based on multiple conditions

    simply, you could tweak it to do

    Code:
    Me.CheckBox1.Visible = (Range(sAddress).Value = "Yes")
    Me.CheckBox1.Visible = (Range(sAddress).Value = "True")
    Me.CheckBox1.Visible = (Range(sAddress).Value = "Correct")
    nothing there to make it false
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    New Member
    Join Date
    Aug 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hiding Button based on multiple conditions

    Hello @mole999,

    first thanks for your answer,

    but can you please right the whole code, because i didn't fully understand what you mean

    Do you mean i have to write only those 3 lines? Because nothing happen when they are alone

    and when i tried to add the 2 other lines under the previous code, the button appear only when the condition in the third line is available...

    Please help me

  4. #4
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,873
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Hiding Button based on multiple conditions

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sAddress As String
    sAddress = "D8"
    
    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.CheckBox1.Visible = (Range(sAddress).Value = "Yes")
    Me.CheckBox1.Visible = (Range(sAddress).Value = "True")
    Me.CheckBox1.Visible = (Range(sAddress).Value = "Correct")
    End With
    
    
    
    CleanUp:
    Application.EnableEvents = True
    
    End Sub
    potentially
    If Range("d8") = "Yes" Or Range("d8") = "True" Or Range("d8") = "Correct" Then CheckBox1.Visible = True
    in place of the three
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  5. #5
    Board Regular
    Join Date
    Nov 2011
    Posts
    1,367
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Hiding Button based on multiple conditions

    The approach I would use I think is just a bit easier to understand and modify.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Const TargetCell = "$D$8"
        If Target.Cells(1, 1).Address = TargetCell Then _
            Me.CommandButton1.Enabled = IsButtonEnabled(TargetCell)
    End Sub
    Function IsButtonEnabled(TargetCell) As Boolean
        Select Case UCase(Range(TargetCell).Text)
            Case "YES", "TRUE", "CORRECT", "OK", "AOK"
                IsButtonEnabled = True
            Case Else
                IsButtonEnabled = False
        End Select
    End Function

  6. #6
    New Member
    Join Date
    Aug 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hiding Button based on multiple conditions

    Dear tlowry,
    I tried using your code, but seriously, nothing happen when i write YES or OK or whatever in the cell, and nothing happen when i delete the cell value, no hide no show...

  7. #7
    New Member
    Join Date
    Aug 2015
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hiding Button based on multiple conditions

    Oh sorry for bothering guys,

    i was doing something wrong,

    tlowry your method is even better than expected

    and mole999 thanks for your contribution

Some videos you may like

User Tag List

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
  •