VBA - Excel - Finding the cell location of a userform TextBox populated by a for i loop?

Question

What do I need to know?

How do I get the location of the data that a textbox is displaying? How do I know where it is?

What am I doing?

I have some code that loops through i and assigns it a value then pulls the cell value from a sheet based on i....so (i, 2) is simply: Row i from Column 2. This is then displayed in a userform Textbox.

What I want to do?

Add a dbl_click event, so that someone can double click on the textbox and be sent to the sheet/row/column that is being displayed. I have no issue creating the dbl_click event, but my problem appears to be how to get the cell location being displayed?

If it is relevant, this is my code for the loop:

Dim code as String
code = search.Value

For i = 2 To LastRow
            
If Sheet1.Cells(i, 9).Value = code Then
     
ssn1.Text = Sheet1.name
hb11.Text = Sheet1.Cells(i, 9).Value
End If
Next i

This is a snippet, as this goes on for awhile, hb11 runs though to hb37 - didn't see any reason to paste it all here.

The problem is, that the loop continues through, across multiple sheets as well, finding all examples of "code" so i keeps changing, after it has written the data to the TextBox - so I can't rely on (i, 9) from the loop.

I have gotten this far in terms of code:

Sub bt11_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   
    If hb11.Value <> ("") Then

    Application.Goto Reference:=Sheet1.Range(hb11)
        
    End If
    
End Sub

However this appears to be relying on the value of hb11, rather than the cell location.

I know this is a dumb question, I know, but I just can't seem to find the answer?

I get the feeling that it lies in:

Dim cell as Range

Then:

Set cell = hb11.something

But I have been through the list, cell/range gives a mismatch, and don't actually exist in the list. There is no 'linked cell' as I thought that might do it...

I am a bit lost.

Answer 1

Profit from using the .Tag property

I'm assuming each of your 27 textboxes refers to exactly one source range address which consists of sheetname, row and column (or column character).

If so you can profit from assigning a combined reference string (e.g. "Sheet22" & "," & i & "," & 9) to a textbox'es ►.Tag property during the initializing loop, e.g. in a comma separated way like perhaps

    hb11.Tag = "Sheet22,17,9"      ' << i.e. sheet name, row 17, column 9

I think it'll be easy to get all data from there:

    Dim src: src = split(hb11,",") 
    Application.Goto Reference:= _
        ThisWorkbook.Worksheets(src(0)).Range(Cells(Val(src(1)), Val(src(2))).Address), Scroll:=True