Hide text boxes not meeting the match with the range

[Cattyyyyy]

Hello,

I am a newbie to VBA and I would really appreciate your help.

I have 120 text boxes with the two letter abbreviations (each unique). They are allocated on the picture of the map. User can input those in the range ("RA2:S23"). I would like to hide all text boxes not meeting the match with the range RA2:S23 (mix of blanks with cell containing two-letter postcodes, they can repeat). Would you be able to help?
Range (example)
LU BV

PT LU

Text boxes (example)
LU PT BZ BV
NN BB CC GH
KL BT VB MM

The names of the text boxes are text box 1, text box 2 ... until text box 120.

Thank you.

 

[My Aswer Is This]

Define "Textbox". Do you mean you have a Userform with 120 text boxes?

 

[Cattyyyyy]

Hello, text boxes are just static shapes (created from the drawing toolbar) placed on the picture (Picture 3).

 

[My Aswer Is This]

I understand now what you mean by Textbox. So for example textbox1 gets it's data from cell ??. And the range RA2:S23 is a very big range and a lot bigger then 120. Please rephrase your question please. I hope I can help you but I don't understand what you are wanting.

 

[Cattyyyyy]

The text boxes have following data: LU, PT, ZZ, BW.. and they are placed on the map. If the user selects for example route: BZ in (r2) and BW(s2), I would like only those two text boxes to appear on the map. I am sorry I am maybe complicating it, but I wanted to just show those text boxes that abbreviations appear in that range

 

[My Aswer Is This]

Ok Catty I think we are getting there. How does the user select route BZ in (r2). And what does (r2) mean? I would think you mean cell (R2)

 

[Cattyyyyy]

The user inserts the first part of the postcode (LU4, BZ67.. etc.) into range ("E2:F23"). For example:
D2: 7:00 E2: LU4 F2: BZ42
D3: 8:00 E3: Blank F3: Blank
D4: 9:00 E4: BZ42 F4: HP5 etc.

I have a formula to calculate the distance between those postcodes. Then in the range ("R2:S23") - I have in each cell =left(E2,2) - which gives R2: LU S2: BZ.. etc.

 

[Alan_P]

Hi Catty,

Do you have any other shapes on the sheet other than textboxes and the picture?

Cheers,
Alan.

 

[Alan_P]

Hi Cathy,

Give the below code a go, as always recommend trying it in a copy of you workbook first...

Sub HideTBoxIfFound()

Dim Rng As Range
Dim n As String
Dim x As Long
Dim i As Long

i = ActiveSheet.TextBoxes.Count

For x = 1 To i
n = ActiveSheet.TextBoxes(x).Text
       
    Set Rng = Range("R2:S23").Cells.Find(What:=n, LookIn:=xlValues, LookAt:=xlPart)
     
    If Rng Is Nothing Then
        ActiveSheet.TextBoxes(x).Visible = False
    Else
        ActiveSheet.TextBoxes(x).Visible = True
    End If

Next

End Sub

If you need to show all the textboxes for updates and the like, you can use this...

Sub ShowAllTBox()

Dim x As Long
Dim i As Long

i = ActiveSheet.TextBoxes.Count

For x = 1 To i
ActiveSheet.TextBoxes(x).Visible = True
Next

End Sub

If you need to hide them all just change the .Visible = True to False

Hope this helps,
Cheers,
Alan.

 

[Cattyyyyy]

Hello Alan,

It works very well! Many thanks for your help!
Kat