Show and Hide Shapes in Active Worksheet

Question

ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2")).Visible = Not ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2")).Visible
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 3")).Visible = Not ActiveSheet.Shapes.Range(Array("Rounded Rectangle 3")).Visible
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 4")).Visible = Not ActiveSheet.Shapes.Range(Array("Rounded Rectangle 4")).Visible
ActiveSheet.Shapes.Range(Array("Rounded Rectangle 5")).Visible = Not ActiveSheet.Shapes.Range(Array("Rounded Rectangle 5")).Visible

This is the sample of code macro that i use

i want to know how we can on run this code to hide only rectangle 1 line

like

For i = 1 To ActiveSheet.Shapes.Count
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle *")).Visible = _ 
    Not ActiveSheet.Shapes.Range(Array("Rounded Rectangle *")).Visible
Next i

I just dont know how to simplify correctly

score 2 · Accepted Answer · 2013-06-28T08:19:15.750

2

Youre not far off , this is how to use the loop to go through all the Rounded Rectangles

Dim i As Long
For i = 1 To ActiveSheet.Shapes.Count
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle " & i)).Visible = _
    Not ActiveSheet.Shapes.Range(Array("Rounded Rectangle " & i)).Visible
Next i

note how "Rounded Rectangle " & i is replaced with the
"Rounded Rectangle 1"
"Rounded Rectangle 2"
"Rounded Rectangle n" where n = i ( meaning the amount of times the loop runs )

therefore, if you want to hide only the first one Rounded Rectangle 1, then add an If/else statement to your loop
note: this code will never display the first Rounded Rectangle 1 and toggle between showing all the other ones. if you wanted to always show them then just assign true inside the else statement

Dim i As Long
For i = 1 To ActiveSheet.Shapes.Count
    If i = 1 Then
        ActiveSheet.Shapes.Range(Array("Rounded Rectangle " & i)).Visible = False
    Else
        ActiveSheet.Shapes.Range(Array("Rounded Rectangle " & i)).Visible = _
        ActiveSheet.Shapes.Range(Array("Rounded Rectangle " & i)).Visible
    End If
Next i

edited Jun 28 '13 at 08:19

answered Jun 28 '13 at 08:12

@eathapeking youre welcome. you can accept the answer clicking the green check mark below the voting counter – Jun 28 '13 at 08:22
It said it wasnt found the that name :( – eathapeking Jun 28 '13 at 08:22
forget dim i as long :) but still error "the item with specific name was not found" – eathapeking Jun 28 '13 at 08:24
oh alright, you should always dimension your variables though it's easier NOT to get lost ;) read more about [Option Explicit](http://msdn.microsoft.com/en-us/library/y9341s4f(v=vs.80).aspx) – Jun 28 '13 at 08:26
it still show "the item with specific name was not found" `:( – eathapeking Jun 28 '13 at 08:41
it is exist when i change it back to use simple "Rounded Rectangle 1" but when change to use for loop "Rounded Rectangle " & i ... it show an error.. i also try using ActiveSheet.Shapes.Range(Array ( i )) it work to hide all shape – eathapeking Jun 28 '13 at 09:07
1

Check the spelling of the name of `rounded rectangle 1`. make sure it does exist by its name – Jun 28 '13 at 09:10
1

ok you may want to use a for each loop instead of an numerical iterator. follow the logic from [this answer](http://stackoverflow.com/questions/16818207/excel-vba-uncheck-all-checkboxes-across-entire-workbook-via-commandbutton/16818828#16818828) – Jun 28 '13 at 09:21

Show and Hide Shapes in Active Worksheet

1 Answers1