I wrote a vba code to hide/unhide charts/list boxes in my active sheet that I assigned to a "button" or just a shape that I pulled from the Insert Tab in excel. This code works with 1 shape, but I can't get it to work with multiple. I'm new to VBA so I'm not sure about all the nuances with the syntax. Here's what I have:
Sub OverviewB()
With ActiveSheet.Shapes("Rounded Rectangle 1").TextFrame2.TextRange.Characters
If .Text = "Hide Overview" Then
.Text = "Show Overview"
ActiveSheet.Shapes("Chart 20", "List Box 1", "Chart 19", "List Box 3", "Chart 22", "List Box 4", "Chart 24", "List Box 5").Visible = False
Else
.Text = "Hide Overview"
ActiveSheet.Shapes("Chart 20", "List Box 1", "Chart 19", "List Box 3", "Chart 22", "List Box 4", "Chart 24", "List Box 5").Visible = True
End If
End With
End Sub
I did have a line seperator "_" in the middle of my chart/list box names so that it wasn't as wide, but I took that out because I thought it might be causing some errors.
Can anyone see where the syntax error is here? The error I receive is: "Runtime Error 450": Wrong number of arguments or invalid property assingment.
The error message gives you some hints, Wrong number of arguments or invalid property assingment. You're sending too many arguments to the .Shapes
property.
The Worksheet.Shapes
property collection takes a single argument and returns a Shapes
object, which is a collection of Shape
objects. From the dox,
Use
Shapes(index)
, whereindex
is the shape’s name or index number, to return a single Shape object.
And further:
Use
Shapes.Range(index)
, whereindex
is the shape’s name or index number or an array of shape names or index numbers, to return aShapeRange
collection that represents a subset of the Shapes collection.
You need to set the elements in an array and specify the items within range
ActiveSheet.Shapes.Range(Array("Chart 20", "List Box 1", "Chart 19", "List Box 3", "Chart 22", "List Box 4", "Chart 24", "List Box 5")).Visible = True