Hide commandbutton based on cell value excel vba

Question

I am trying to hide a commandbutton based on a specific cell value. I have looked up several codes and pasted them in excel (in the vba form when right clicking the sheet and selecting "view code").

What am I doing wrong?

Here's one of the codes I've tried:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1") = 0 Then ActiveSheet.CommandButton1.Visible = False
If Range("A1") = 1 Then ActiveSheet.CommandButton1.Visible = True
End Sub

Answer 1

Make sure you enable events before using your code. Also, you must place your code in Worksheet module, not in regular module. To enable events, use this simple sub.

Sub Enable_events()
Application.EnableEvents = True
End Sub

Answer 2

Please try this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Selection.Cells.Count = 1 Then
            If Range("A1") = 0 Then ActiveSheet.CommandButton1.Visible = False
            If Range("A1") = 1 Then ActiveSheet.CommandButton1.Visible = True
        End If
    End If

End Sub

Hope this help.

Answer 3

please run this first:

Sub enable_()
Application.EnableEvents = True
End Sub

and then your Code will run perfectly:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1") = 0 Then ActiveSheet.CommandButton1.Visible = False
If Range("A1") = 1 Then ActiveSheet.CommandButton1.Visible = True
End Sub

Answer 4

Your code is confusing, for a number of reasons.

Range, when it's not qualified with a Worksheet object, implicitly refers to the ActiveSheet, i.e. ActiveSheet.Range... but when it's in a worksheet's code-behind, it implicitly refers to that worksheet's Range property, i.e. Me.Range. Because the meaning of an unqualified Range call depends on context, it's best to always qualify it with an explicit Worksheet object.

So if you're in the code-behind module for Sheet1, then Range("A1") is equivalent to Sheet1.Range("A1"), or even better, Me.Range("A1").

The two conditions will be evaluated every time, but only one of them needs to be: it's inefficient.

Truth is, you don't need to assign a Boolean literal - a Boolean expression is much cleaner.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.CommandButton1.Visible = (Me.Range("A1") = 1)
End Sub

Now, assuming Application.EnableEvents returns True, that code will run every time the selection changes, which is rather overkill.

Handle the Worksheet.Change event instead, and only act when the modified cell is A1:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.Intersect(Target, Me.Range("A1")) Is Nothing And Target.Count <> 1 Then
        ' we don't care about that cell: bail out
        Exit Sub
    End If

    Me.CommandButton1.Visible = (Me.Range("A1") = 1)

End Sub