Where am I going wrong in my loop / passing a value to a textbox? Help! :)

AdamKam · Nov 13th 2021

[xpost]

[/xpost]

Please read: https://www.excelguru.ca/content.php?184

crossposted at: https://www.ozgrid.com/forum/c…hp?moderation-report/542/

Hi all,

I am trying to learn and am very new at this however I would appreciate some expert help! I am trying to make a simple loop to search sheet1 and display the results on sheet2 which works and that gives me the desired result, however I am creating dynamic hyperlinks in the results on sheet2 which link to sheet 3 where I trying to populate the text box with a value from the results in which a particular hyperlink is pressed. The hyperlink text displays the correct value in the text but when ever I try pass the value to the textbox anywhere in my loop, its always the last value from the loop. I have attached my test workbook so you can see what I mean..

On sheet2 I search for a courseID which then displays my results. In this test workbooks case, I search for 123.

I get 3 lots of results. Within those results is a unique DCN number, 111, 222, and 333 which I am trying to pass to the textbox depending on which hyperlink you click.

No matter what I do, I always get 333 in the textbox and after many hours of trying work out where I go wrong its hurting my head. lol

I hope what I say makes sense in what I am trying to say, and you can understand what I am trying to achieve! Hopefully the attached workbook shows exactly what i mean, I would REALLY appreciate if someone can have a looksie for me and tell me where I go wrong? All I want is my little number to show up in textbox. lol

Please and thank you muchly in advance knowledgeable legends for any help you may be able to give.

**royUK** · Nov 13th 2021

Why do you need to use a TextBox?

**royUK** · Nov 13th 2021

Don't use the ActiveX TextBox. Change it to a Forms one.

Code

Sub MultipleResults()
    Dim WS As Worksheet
    Dim lastRow As Long
    Dim count As Integer
    Dim dcn As String
    
    ws_output = "SHEET2"
    ws_input = "SHEET1"
    ws_test = "SHEET3"
    
    lastRow = Sheets(ws_input).Cells(Rows.count, 1).End(xlUp).Row
    
    count = 0
    Dim p As Long
    Dim q As Long
    Dim r As Long
    Dim s As Long
    Dim t As Long
    
    p = 18
    q = 19
    r = 20
    s = 21
    t = 22
    
    For x = 6 To lastRow
        
        If Sheets(ws_input).Cells(x, 6) = Sheets(ws_output).Range("B2") Then
            
            Sheets(ws_output).Cells(s, 4) = Sheets(ws_input).Cells(x, 2)
            Sheets(ws_output).Cells(p, 4) = Sheets(ws_input).Cells(x, 1)
            Sheets(ws_output).Cells(q, 4) = Sheets(ws_input).Cells(x, 3)
            
            dcn = Sheets(ws_output).Cells(s, 4)
            
            Sheets(ws_output).Cells(s, 10).Hyperlinks.Add Anchor:=Sheets(ws_output).Cells(s, 10), Address:="", SubAddress:="'SHEET3'!A1", TextToDisplay:="Click to edit DCN: " & dcn
            
            Sheets("Sheet3").Shapes("TextBox 1").TextFrame.Characters.Text = dcn
            
            
            p = p + 6
            q = q + 6
            r = r + 6
            s = s + 6
            t = t + 6
            
            count = count + 1
            
        End If
        
    Next x
    
End Sub

Display More

AdamKam · Nov 13th 2021

Thank you, I got it working!

Where am I going wrong in my loop / passing a value to a textbox? Help! :)

Participate now!

Share