Hide/unhide specific objects in Excel with VBA

Question

I want to have a macro to hide/unhide callouts.

The intention is to have an information button that once presses show or hide the information callouts.

The problem is that I have other arrows and shapes that I don't want to be hidden.

With the following code (1) I can hide all objects:

Dim sObject As Shape
For Each sObject In ActiveSheet.Shapes
    If sObject.Visible = False Then
        sObject.Visible = True
    Else
        sObject.Visible = False
    End If
Next

And with this code (2) I can hide/unhide specific callout shapes

If ActiveSheet.Shapes("Rectangular Callout 6").Visible = False Then
    ActiveSheet.Shapes("Rectangular Callout 6").Visible = True
Else
    ActiveSheet.Shapes("Rectangular Callout 6").Visible = False
End If

How can I have the first code (1) to run through the callout shapes only like in the second code (2)?

R3uK · Accepted Answer · 2017-02-28 08:36:12Z

3

How about:

Dim sObject As Shape
For Each sObject In ActiveSheet.Shapes
   If Not InStr(sObject.Name, "Callout") = 0 Then sObject.Visible = Not sObject.Visible
Next sObject

Hope it helps!

edited Feb 28, 2017 at 8:36

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

answered Feb 28, 2017 at 8:03

Mihai Ovidiu Drăgoi

1,3171 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Selrac Over a year ago

Thanks Mahai, but the if not line appears as an error 'If Not InStr(sObject .Name, "Callout") = 0 Then'

Mihai Ovidiu Drăgoi Over a year ago

Edited, should be ok now

Selrac Over a year ago

Yes, this is perfect. Thanks

R3uK · Accepted Answer · 2017-02-28 08:05:53Z

2

As the visible property is a boolean, you can shorten your code :

Sub InvertAllShapesVisibility(wS As Worksheet)
    Dim sObject As Shape
    '''Invert visibility of all shapes
    For Each sObject In wS.Shapes
        sObject.Visible = Not sObject.Visible
    Next sObject
End Sub

How to use it :

Sub Test1_Selrac()
    InvertAllShapesVisibility ActiveSheet
End Sub

And for a single shape :

Sub RevertShapeVisibility(wS As Worksheet, ShapeName As String)
    Dim sObject As Shape
    '''Invert visibility of all shapes containing the KeyWord
    For Each sObject In wS.Shapes
        If sObject.Name = ShapeName Then sObject.Visible = Not sObject.Visible
    Next sObject
End Sub

How to use it :

Sub Test2_Selrac()
    RevertShapeVisibility ActiveSheet, "Rectangular Callout 6"
End Sub

And for multiple shapes containing keywords :

Sub RevertCalloutsVisibility(wS As Worksheet, KeyWord As String)
    Dim sObject As Shape
    '''Invert visibility of one shape
    For Each sObject In wS.Shapes
        If Instr(1,sObject.Name,KeyWord) Then sObject.Visible = Not sObject.Visible
    Next sObject
End Sub

How to use it :

Sub Test3_Selrac()
    RevertCalloutsVisibility ActiveSheet, "Rectangular Callout"
End Sub

edited Feb 28, 2017 at 8:05

answered Feb 28, 2017 at 8:00

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

4 Comments

Selrac Over a year ago

R3uK, thanks, but I need only the 'Rectangular Callout' shapes to be hidden/unhidden

R3uK Over a year ago

@Selrac : See Test3! ;)

Selrac Over a year ago

Thanks R2uK. That is what I was looking for. You are very good.

R3uK Over a year ago

@Selrac : Glad I could help! ;)

Collectives™ on Stack Overflow

Hide/unhide specific objects in Excel with VBA

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related