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]

Are you trying to do this on all sheets, or just the activesheet?

 

[kcstratslinger]

Are you trying to do this on all sheets, or just the activesheet?

all sheets

 

[Fluff]

In that case try changing

Me.Shapes("Oval1")

to

shp

 

[kcstratslinger]

In that case try changing

Me.Shapes("Oval1")

to

shp

I changed it to 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 shp.Shapes("Oval1").TextFrame.Characters.Text = "0" Then
                    shp.Shapes("Oval1").Visible = False
                Else
                    shp.Shapes("Oval1").Visible = True
                End If
            Next shp
        End If
    Next sh
End Sub

I get an error saying "Object doesn't support this property or method" on the line "If shp.Shapes("Oval1").TextFrame.Characters.Text = "0" Then'

 

[kcstratslinger]

Hope this OneDrive link works. The sheet named "Modes Main" is the sheet with the shapes. But I would like it to work across the entire workbook because I will be adding more sheets with shapes.

Guitar Lessons

 

[Fluff]

You need to get rid of .Shapes("Oval1")so you just have

shp.TextFrame.Characters.Text

and

shp.Visible

 

[kcstratslinger]

I get "Application-defined or Object-defined" error. The thing is, I looks like it worked for a second. Multiple shapes disappeared when I changed the drop-down labeled Key at the top of the page. But I am getting this error and it is not working now.

 

[Fluff]

What is your code as it stands?

 

[kcstratslinger]

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 shp.TextFrame.Characters.Text = "0" Then
                    shp.Visible = False
                Else
                    shp.Visible = True
                End If
            Next shp
        End If
    Next sh
End Sub