Making shapes invisible/visible in excel through VBA

Question

I'm having this problem for the last few hours and I would really appreciate some help with it.

Basically, I want to be able to hide/unhide shapes depending on selections a user makes on a userform. I've broken the problem down into a very simple example. If I insert a shape called "oval 1" in a sheet and run the code:

Sub hideshape()

    With ActiveSheet

        .Shapes("Oval 1").Select

        With Selection

        .Visible = False

        End With

    End With

End Sub

the shape disappears but when I run this code

Sub unhideshape()

    With ActiveSheet

        .Shapes("Oval 1").Select

        With Selection

        .Visible = True

        End With

    End With

End Sub

I get an error "Requested Shapes are locked for Selection"

The workbook is not protected and I have tried un-ticking locked and locked text on the shape properties.

Any ideas what's causing this.

Answer 1

You cannot Select a hidden object. However, you dont need to use Select at all, and it is usually not recommended. Try simply:

Sub HideShape()
    ActiveSheet.Shapes("Oval 1").Visible = False
End Sub
Sub UnhideShape()
    ActiveSheet.Shapes("Oval 1").Visible = True
End Sub

Answer 2

Sub HideEachShape()
Dim sObject As Shape
For Each sObject In ActiveSheet.Shapes
sObject.Visible = False
Next
End Sub

from: extendoffice.com