Loop Shapes and Hide if Value/Text is Zero

kcstratslinger

New Member
Joined
Dec 12, 2020
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
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:

VBA Code:
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 :)
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Are you trying to do this on all sheets, or just the activesheet?
 
Upvote 0
In that case try changing
VBA Code:
Me.Shapes("Oval1")
to
VBA Code:
shp
 
Upvote 0
In that case try changing
VBA Code:
Me.Shapes("Oval1")
to
VBA Code:
shp
I changed it to this:

VBA Code:
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'
 
Upvote 0
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
 
Upvote 0
You need to get rid of .Shapes("Oval1")so you just have
VBA Code:
shp.TextFrame.Characters.Text
and
VBA Code:
shp.Visible
 
Upvote 0
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.
 
Upvote 0
What is your code as it stands?
 
Upvote 0
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,215,480
Messages
6,125,049
Members
449,206
Latest member
Healthydogs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top