CODEWITHSUNDEEP
vbaexcel
Nathan
Nathan

Reputation: 21

Button.Visible=False not working in VBA

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

Upvotes: 2

Views: 2528

Answers (2)

user3819867
user3819867

Reputation: 1118

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

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166860

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

Upvotes: 2

Related Questions