CODEWITHSUNDEEP
excelvba
Matthew Brophy
Matthew Brophy

Reputation: 75

Making shapes invisible/visible in excel through VBA

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.

Upvotes: 7

Views: 75338

Answers (5)

tpjmlu26
tpjmlu26

Reputation: 21

I hide shapes based on their name since some shapes I don't want to hide. I use this format: Sheet1.Shapes.Range(Array("COtxtBox1")).Visible = msoTrue

name of your shape or shapes goes into the array

if it only 1 shape you could just use:

Sheet1.Shapes.range("COtxtBox1").Visible = True

I found that the "mso" part is not necessary for the True or False statement

Upvotes: 2

Armand Villa
Armand Villa

Reputation: 21

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

from: extendoffice.com

Upvotes: 1

TiIiMuRkA
TiIiMuRkA

Reputation: 1

I solved problem with this code(Oval = Type 9, from MsoAutoShapeType Enumeration (Office)):

Sub hide()
s = ActiveSheet.Shapes.Count
For i = 1 To s
    If ActiveSheet.Shapes(i).Type = 9 Then ActiveSheet.Shapes(i).Visible = False
Next i
End Sub

Sub unhide()
s = ActiveSheet.Shapes.Count
For i = 1 To s
    If ActiveSheet.Shapes(i).Type = 9 Then ActiveSheet.Shapes(i).Visible = True
Next i
End Sub

If "Type = 9" is wrong, you can find out type of your shape with code in Immediate window (ctrl+G in VBA):

?ActiveSheet.Shapes("Oval 1").Type

Upvotes: 0

Muhammad Kafiansyah
Muhammad Kafiansyah

Reputation: 1

Public HIDE As Boolean

    Sub T_BUTTON ()
    ActiveSheet.Shapes("T 1").Visible = HIDE
    If ActiveSheet.Shapes("T 1").Visible = False Then
    HIDE = True
    Else
    HIDE = False
    End If

END SUB

Upvotes: -1

A.S.H
A.S.H

Reputation: 29332

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

Upvotes: 12

Related Questions