Hiding Button based on multiple conditions

[Jihad Akoury]

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

 

[mole999]

simply, you could tweak it to do

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

 

[Jihad Akoury]

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

 

[mole999]

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

 

[tlowry]

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

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

 

[Jihad Akoury]

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

 

[Jihad Akoury]

Oh sorry for bothering guys,

i was doing something wrong,

tlowry your method is even better than expected

and mole999 thanks for your contribution