VBA Toggle button to remove shapes on worksheet.

[Certified]

Hello.

I have a number of shapes on my worksheet (text boxes, arrows and so on). I am trying to create a button that when pressed will hide (visible =false) the shapes on the worksheet. And when pressed again will show the shapes again.

Can someone help me with that?

Thanks

 

[davesexcel]

What's your code look like to set the visibility to false?

 

[davesexcel]

See how this works,

It hides or unhides all shapes

    If ActiveSheet.DrawingObjects.Visible = True Then
        ActiveSheet.DrawingObjects.Visible = False
    Else: ActiveSheet.DrawingObjects.Visible = True
    End If

 

[Certified]

The only part I was able to figure out is how to set the visibility to false.

ActiveSheet.Shapes("").Visible = False

So basically i want to code to look like this.

Button is selected ON

ActiveSheet.Shapes("").Visible = False

Button is selected OFF

ActiveSheet.Shapes("").Visible = True

 

[JLGWhiz]

The syntax for the button will vary depending on what type of button you used. The example below uses a Forms control button. If you used an Active-X button then the name would be CommandButton#, with the # representing whatever index number it is. This allows you to hide all shapes, except the button that runs the macro.

Sub delShps()
Dim sh As Worksheet
Set sh = ActiveSheet
    For Each shp In sh.Shapes
        If shp.Name <> "Button 1" Then
            shp.Visible = False
        End If
    Next
End Sub

To make them visible again, change the False to True.

 

[Certified]

Hi,

Thanks, but I wanted to be able to toggle between visible and not visible to pressing the button.

 

[JLGWhiz]

Hi,

Thanks, but I wanted to be able to toggle between visible and not visible to pressing the button.

The problem is that the button itself is a shape, so if you use something like.

Sub shps()
If ActiveSheet.Shapes.Visible = True Then
	ActiveSheet.Shapes.Visible = False
Else 
	ActiveSheet.Shapes.Visible = True
End If

Which is the normal toggle syntax, then you will hide your button also. So that won't work. You almost certainly have to use a loop to do what you want. I am not sure what criteria could be used to do what you want.