Loop Shapes and Hide if Value/Text is Zero

[kcstratslinger]

I am trying to create a sheet to print scales and chords for guitar students. I am having trouble hiding shapes on the fretboard based on the value within the shape. There are 224 shapes on the worksheet. Each shape is linked to it's own cell on a separate sheet. I figured out how to hide a shape based on it's fellow cell value. But I am trying not to write a separate IF statement for each shape/cell. I can either make each shape a different name or make all shapes the same name. Whichever works out to involve the least amount of code. So far I have this:

Sub Worksheet_Change(ByVal Target As Range)
    
Dim shp As Shape
Dim sh As Worksheet

    For Each sh In ActiveWorkbook.Worksheets
        If sh.Shapes.Count > 0 Then
            For Each shp In sh.Shapes
                If Me.Shapes("Oval1").TextFrame.Characters.Text = "0" Then
                    Me.Shapes("Oval1").Visible = False
                Else
                    Me.Shapes("Oval1").Visible = True
                End If
            Next shp
        End If
    Next sh
End Sub

The problem is that if each shape has the same name (Oval1), the code only works for the first shape in the selection pane. If I make each shape a different name (Oval1, Oval2, Oval3,etc.), then I need a way for the loop to search for the next shape name. It would also be best if the code worked for every shape in the workbook. Because I will have other sheets with different scales/chords that will need the same treatment.

I would upload the excel file to this post, but I am not sure how to do that.

Thank you for any help you can offer

 

[Fluff]

when I changed the drop-down labeled Key

What sort of drop down is it?

 

[kcstratslinger]

It is a data validation list that contains a list of keys (C, D, F#, Bb...). There is another list as well that contains the scale (Major, Harmonic Major, etc.) When the Iist selections change then the values in the sheets that the shapes get their values from change.

 

[Fluff]

Ok, try it like

    For Each sh In ActiveWorkbook.Worksheets
        If sh.Shapes.Count > 0 Then
            For Each shp In sh.Shapes
               If Not shp.Name Like "Drop*" Then
                  If shp.TextFrame.Characters.Text = "0" Then
                      shp.Visible = False
                  Else
                      shp.Visible = True
                  End If
               End If
            Next shp
        End If
    Next sh

 

[kcstratslinger]

That worked perfect. Not sure what it all means...lol. But thank you very much! This should be all I need to finish this up.

 

[Fluff]

You're welcome & thanks for the feedback.