Conditional hiding of many text boxes

Slangy

New Member
Joined
Oct 22, 2019
Messages
3
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. ;)

Code:
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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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?
 
Upvote 0
Alternatly, one could put the address of the linked cell in the .AlternativeText property of the matching textbox and loop through that.


Code:
Dim oneShape as Shape

For Each oneShape in ActiveSheet.Shapes
    oneShape.Visible = iif(Range(oneShape.AlternativeText).Value = 0, msoFalse, msoTrue)
Next oneShape
 
Last edited:
Upvote 0
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.
 
Upvote 0
Glad you sorted it & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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