Button.Visible=False not working in VBA

Question

I have a macro enabled workbook, and in the past it has always worked fine, but ever since I had a windows update the macro has caused the workbook to crash, giving me an error of "Run-time error '424': Object Required." I cant seem to figure out why my code in no longer working. Here is my code.

Private Sub Worksheet_Change(ByVal Target As Range)
     'Rage Button
    If Cells(1, 12).Value = "Barbarian" Then 'CHANGE THE CELL TO THE ADRESS OF THE TRIGGER CELL
        Rage.Visible = True 'CHANGE TO THE NAME OF THE BUTTON
    Else
        Rage.Visible = False 'CHANGE TO THE NAME OF THE BUTTON
    End If

'Raging Brutality
    If Cells(1, 12).Value = "Barbarian" Then
        If WorksheetFunction.CountIf(Range(Cells(40, 1), Cells(61, 1)), "Raging Brutality") Then
        Brutality.Visible = True
        Else
        Brutality.Visible = False
        End If
    Else
        Brutality.Visible = False
    End If
     'Sneak Button
    If Cells(1, 12).Value = "Rogue" Then 'CHANGE THE CELL TO THE ADRESS OF THE TRIGGER CELL
        Sneak.Visible = True 'CHANGE TO THE NAME OF THE BUTTON
    Else
        Sneak.Visible = False 'CHANGE TO THE NAME OF THE BUTTON
        Cells(25, 7).Value = 0
    End If
     End Sub

http://stackoverflow.com/questions/27411399/microsoft-excel-activex-controls-disabled — Tim Williams, Mar 04 '15 at 00:08

score 2 · Answer 1 · answered Mar 04 '15 at 01:45

Not the answer to your particular problem, but since Visible is a Boolean property your code can be reduced:

Private Sub Worksheet_Change(ByVal Target As Range)

    Rage.Visible = (Cells(1, 12).Value = "Barbarian") 

    Brutality.Visible = (Cells(1, 12).Value = "Barbarian" And _
                   WorksheetFunction.CountIf( _
                   Cells(40, 1).Resize(22,1), "Raging Brutality") > 0)

    Sneak.Visible = (Cells(1, 12).Value = "Rogue")

    If Not Sneak.Visible Then Cells(25, 7).Value = 0

End Sub

score 0 · Answer 2 · edited May 23 '17 at 12:20

You either want to start by defining your objects

Rage = ActiveSheet.Shapes("Rage")

or refer to them by their accepted format, e.g.

ActiveSheet.Shapes("Rage").Visible = False

.
Using Tim Williams' snipplet your code will look like:

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.Shapes("Rage").Visible = (Cells(1, 12).Value = "Barbarian") 

    ActiveSheet.Shapes("Brutality").Visible = (Cells(1, 12).Value = "Barbarian" And _
                   WorksheetFunction.CountIf( _
                   Cells(40, 1).Resize(22,1), "Raging Brutality") > 0)

    ActiveSheet.Shapes("Sneak").Visible = (Cells(1, 12).Value = "Rogue")

    If Not ActiveSheet.Shapes("Sneak").Visible Then Cells(25, 7).Value = 0

End Sub

Make sure your objects are named Rage, Brutality and Sneak by checking the name box after selecting them.

UPDATE: I made an even more clear and concise sub.
Seems like you can't rename the buttons, just look at their names and replace with Ctrl+H, for each of the names(Rage, Brutality, Sneak).

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Shapes("Rage").Visible = False 'let's make all invisible before evaluation
ActiveSheet.Shapes("Brutality").Visible = False
ActiveSheet.Shapes("Sneak").Visible = False

Select Case Cells(1, 12).Value
Case "Barbarian"
    ActiveSheet.Shapes("Rage").Visible = True
    If WorksheetFunction.CountIf(Range(Cells(40, 1), Cells(61, 1)), "Raging Brutality") Then
    ActiveSheet.Shapes("Brutality").Visible = True
    End If
Case "Rogue"
    ActiveSheet.Shapes("Sneak").Visible = True
Case Else
    Cells(25, 7).Value = 0
End Select
End Sub

[see my example file](https://drive.google.com/file/d/0B-773Ttgo1z6OTM5TGYzOXhONTQ/view?usp=sharing) — user3819867, Mar 04 '15 at 12:34

Button.Visible=False not working in VBA

2 Answers2