Conditional hiding of many text boxes

[Slangy]

Hi all,

I have very little coding expertise and have searched the web and the forum, but couldn't quite find the solution.
I am trying to show/hide hundreds of text boxes based on their values.

Use case:
I have an image of a map (jpg), which includes 222 locations. Each of these locations has a text box (simply placed on top of the image) to display the number of issues at this location. The issues per location are manually entered on a different sheet and are updated from time to time.
Each text box acts like a location label and is linked to the input cell, so it displays the respective cell value. The use case is to use the image with all text boxes in presentations. There is no need to interact with the boxes (other than display).

I am trying to hide the text box when a location has no issues (value: 0).
Whenever a cell value is changed (Range E4:E222), a macro should run and hide the boxes with no value.

My current code works (created from various posts I found), but I am trying to avoid entering this hundreds of times.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range


    Set KeyCells = Range("E4:E225")


If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then


If Range("E4") = 0 Then
ActiveSheet.Shapes.Range(Array("[LOCATION_1]")).Visible = msoFalse
Else
ActiveSheet.Shapes.Range(Array("[LOCATION_1]")).Visible = msoTrue
End If

End If
End Sub

[Location_1] refers to the text box name. Theoretically I would have to create this 222 times, but surely there is a better way!?
Each text box is named after the real-world location to make identification easier. So, ideally, I can simply refer to a simple list of 222 names (by copy/paste) in the code. The list of locations very rarely changes, but could.

I hope this makes sense.
I appreciate your help!

 

[Fluff]

Can put the name of the textbox in F4:F225 next to it's value?
Also you said the values & textboxes are on different sheets, but your code is looking at the same sheet. If they are on different sheets, what are the sheet names?

 

[mikerickson]

Alternatly, one could put the address of the linked cell in the .AlternativeText property of the matching textbox and loop through that.

Dim oneShape as Shape

For Each oneShape in ActiveSheet.Shapes
    oneShape.Visible = iif(Range(oneShape.AlternativeText).Value = 0, msoFalse, msoTrue)
Next oneShape

 

[Slangy]

Thank you for your suggestions!
I ended up referring to each text box individually. Works perfectly, and as the locations never change, I figured it was easiest to do.

 

[Fluff]

Glad you sorted it & thanks for the feedback