I have the following code in a workbook I am currently working on that hides certain shape files. The macro works as intended. The issue I have is that there are 200 shapes per work book and 6 workbook so I would prefer to not have to write the code for 1200 shapes. Is there a way to modify this code that will allow it to look for all shapes at once. I am not concerned about the name of the shape at all as long is it is unique. Essentially if the cell the shape is associated with is empty I would like to hide the shape.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("e3:e203")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
If Range("E3").Value = 0 Then
ActiveSheet.Shapes("P-S1").Visible = False
Else
ActiveSheet.Shapes("P-S1").Visible = True
End If
If Range("E4").Value = 0 Then
ActiveSheet.Shapes("P-S2").Visible = False
Else
ActiveSheet.Shapes("P-S2").Visible = True
End If
End If
End Sub
To further make my life easier is there a way in which I can auto name the shapes to a specific scheme. Liek I sadi I am not picky on names as long as they follow a logical scheme. You will have to bear with me on my ignorance of excel as I am an AutoCAD guy!
Bookmarks