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

What line has an error/highlights?

Chrismas007
– Chrismas007

2015-03-03 23:07:57 +00:00
Commented Mar 3, 2015 at 23:07 — Chrismas007
– Chrismas007, Commented Mar 3, 2015 at 23:07
stackoverflow.com/questions/27411399/…

Tim Williams
– Tim Williams

2015-03-04 00:08:16 +00:00
Commented Mar 4, 2015 at 0:08 — Tim Williams
– Tim Williams, Commented Mar 4, 2015 at 0:08

Tim Williams · Accepted Answer · 2015-03-04 01:45:46Z

2

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

answered Mar 4, 2015 at 1:45

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:20:37Z

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

Collectives™ on Stack Overflow

Button.Visible=False not working in VBA

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related