There must be a way to shorten this!

[m_in_spain]

Hi
I have some shapes (arrows = called drop1, drop2.... etc) on my spreadsheet, which i want to appear/disappear depending on the value of cell C4. This below works ok but to me seems long winded, especially considering i need to add another hundred arrows or so, and then duplicate the whole thing for cell C7.
The only thing that changes is this end number for the shape. I have tried all manner of ways to change this but all fail. There must be a way! If any clever person can help I will be most grateful.

Private Sub Worksheet_Calculate()If Range("C4") = 3 Then
Me.Shapes("drop1").Visible = True
Me.Shapes("drop2").Visible = True
Me.Shapes("drop3").Visible = True
Me.Shapes("drop4").Visible = True
Me.Shapes("drop5").Visible = True
Me.Shapes("drop6").Visible = True
Me.Shapes("drop7").Visible = True
Me.Shapes("drop8").Visible = True
Else
Me.Shapes("drop1").Visible = False
Me.Shapes("drop2").Visible = False
Me.Shapes("drop3").Visible = False
Me.Shapes("drop4").Visible = False
Me.Shapes("drop5").Visible = False
Me.Shapes("drop6").Visible = False
Me.Shapes("drop7").Visible = False
Me.Shapes("drop8").Visible = False
End If
End Sub

 

[FormR]

Hi, how about something like:

Private Sub Worksheet_Calculate()
Dim sh As Shape
For Each sh In Me.Shapes
    If sh.Name Like "drop*" Then sh.Visible = (Range("C4") = 3)
Next sh
End Sub

 

[m_in_spain]

You are a genius, it works like a dream, many thanks.

Now if i want some drops (for eg drop8) to appear when C6 is changed, but is unaffected by any change in C3
To me the obvious answer is to use a different name (for eg fall8 etc) but it would be nice to do it all neatly using just one description?

Thanks in advance!

Hi, how about something like:

Private Sub Worksheet_Calculate()
Dim sh As Shape
For Each sh In Me.Shapes
    If sh.Name Like "drop*" Then sh.Visible = (Range("C4") = 3)
Next sh
End Sub

 

[FormR]

To me the obvious answer is to use a different name (for eg fall8 etc)

That sounds like a perfectly sensible way to do it, but if you really did want to hardcode the names of the shapes to exclude, you could do something like this.

Private Sub Worksheet_Calculate()
Dim exclude As Variant
exclude = Array("drop8", "drop10") '< Add more shape names to exclude here 
Dim sh As Shape
For Each sh In Me.Shapes
    If sh.Name Like "drop*" Then
        If Not IsNumeric(Application.Match(sh.Name, exclude, 0)) Then sh.Visible = (Range("C4") = 3)
    End If
Next sh
End Sub

 

[m_in_spain]

OK, many thanks again.
As i sometimes want them to appear, sometimes not i will go along with the different name for each set route.
Thanks again for your help