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

This code could be simplified to `Me.CommandButton1.Visible = (Me.Range("A1") = 1)`. Place a breakpoint (F9) in that handler procedure, and make sure it's written in the correct worksheet's code-behind. Answers telling you to set `Application.EnableEvents = True` aren't wrong - the event won't fire if application events are disabled. But you need to first make sure you've written the code in the correct worksheet: your post makes no indication of that (or that application events would have been disabled, for that matter). — Mathieu Guindon, Sep 18 '17 at 14:09

score 0 · Answer 1 · answered Sep 18 '17 at 13:28

0

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

answered Sep 18 '17 at 13:28

Kresimir L.

2,301
2
10
22

1

Procedure names should be `PascalCase`, like they are in every VBA type library out there. Underscore in member name denotes an interface member or event handler implementation; you'll run into compilation errors when you start working with classes and interfaces if you make a habit of using underscores in procedure names. – Mathieu Guindon Sep 18 '17 at 14:08

score 0 · Answer 2 · answered Sep 18 '17 at 14:02

0

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

answered Sep 18 '17 at 14:02

Nikolaos Polygenis

591
7
13

Why the trailing underscore? Procedure names should be `PascalCase`, like they are in every VBA type library out there. Underscore in member name denotes an interface member or event handler implementation; you'll run into compilation errors when you start working with classes and interfaces if you make a habit of using underscores in procedure names. – Mathieu Guindon Sep 18 '17 at 14:07

score 0 · Answer 3 · answered Sep 18 '17 at 14:23

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

@Downvoter please let me know how I can improve this answer. Unqualified Range calls cause a myriad of errors all the time, so F off. — Mathieu Guindon, Sep 18 '17 at 14:33

score -1 · Answer 4 · answered Sep 18 '17 at 13:34

-1

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.

answered Sep 18 '17 at 13:34

adhy wijaya

509
3
7

What is `Selection` doing here? `ActiveSheet` is too broad, and unqualified `Range` is ambiguous/confusing. Otherwise good answer, but lacking explanation. Avoid "try this: {code dump}" answers, *especially* when the OP admits copy+pasting random stubs from strangers on the Internet. They need a fishing rod, not a fish. – Mathieu Guindon Sep 18 '17 at 14:25

Hide commandbutton based on cell value excel vba

4 Answers4