Populate Textbox from offset cells when selecting listbox item

Apologies if this is already here, but I have searched and cannot find a solution for my situation!

I have a userform with a listbox showing 3 columns. I would like to populate a text box with data when I select a row in the listbox. Columns 1 through 3 are shown in the listbox. When I click on an item I want column 4 from the row to be shown in the textbox.

The listbox is populated using this code: -

Private Sub UserForm_activate()

      Dim MyList(200, 3) 'as array type
      Dim R As Integer
   
      Application.ShowToolTips = True
      With ListBox1
            .ColumnCount = 3
            .ColumnWidths = 180
            .Width = 230
            .Height = 110
            .ControlTipText = "Click the Name, Job, or ID you're after"
      End With

      'Define the list and where it's obtained from
     
      With ActiveSheet

             For R = 0 To 200
                  MyList(R, 0) = .Range("C" & R + 5)
                  MyList(R, 1) = .Range("B" & R + 5)
                  MyList(R, 2) = .Range("H" & R + 5)
            Next R
      End With

     'populate the list box
      ListBox1.List = MyList

When I click on an item in the listbox this code is executed: -

Private Sub listBox1_Click()
      
      Dim NameFound As Range
      Dim AddFound As Range
      Dim fPath As String
      
    'selects the range to look for a name.  
    With Range("myName")
        Set NameFound = .Find(ListBox1.Value)
        TextBox1.Text = .Find(ListBox1.Value).Offset(0, 6)
        On Error Resume Next
        If NameFound Is Nothing Then
            Image1.Picture = LoadPicture(fPath & "nopic.gif")
        Else
            With NameFound
                'Look in the directory where this workbook is located.
                fPath = ' "filepath of workbook"
                On Error Resume Next
                'If a matching picture is found then display it.
                Image1.Picture = LoadPicture(fPath & ListBox1.Value & ".jpg")
                'If No picture found then display the default picture.
                If Err = 0 Then Exit Sub
                Image1.Picture = LoadPicture(fPath & "nopic.gif")
                
               
                
    End With
    End If
    End With
    
    
   
    Set NameFound = Nothing
   
    
    
End Sub

The above code also populates an image holder with a photo from the filepath of the worksheet matching the name of the item I click in the listbox. The problem I'm having however is with this line: -

TextBox1.Text = .Find(ListBox1.Value).Offset(0, 6)

All I get is a copy of the text shown in the listbox printed in the textbox. It appears to be ignoring the offset. It doesn't matter what I change it to!

Ideally what I want is: -

For columns 1 - 3 to show in listbox and when I click on it, column 4 (not shown in listbox) to populate textbox1.

Col1 Col2 Col3 Col4(not shown in listbox)
Name DOB Gender Address

Many Thanks in advance!

Thanks for your reply, would this work however when the listbox is not being populated with that data? I'm only populating it with 3 columns?

Sorry, but confused (very new to this!), but I'm not using RowSource to populate the listbox. I'm using a defined Name Range in the worksheet which I then define in the code "NameFound". This is a defined range of 1 column in the worksheet. I am still able to populate the listbox with as many columns as I want, but just use the defined range when searching it.

Even why I try this way, I still just get a copy of what the first column in the listbox says, printed in the textbox: -

    Dim MyName As String, myRange As Range
    Dim found As Range
    MyName = Me.ListBox1.Value
    Set myRange = ThisWorkbook.Sheets("Scoring").Range("A:H")
    Set found = myRange.Find(MyName, LookIn:=xlValues, LookAt:=xlWhole)
   
        Me.TextBox1.Text = found.Offset(, 1)

Hi,

Thanks for that, I've tried doing as you said but I am still only seeing the data from column 0 being printed in the textbox...am very puzzled by this! I've tried changing column numbers and offsets etc, but still no joy. Am I missing something?! I would post the entire workbook for you to look at, but as it is an already populated sheet with sensative data I am unable to do so. Any other ideas?

Ok cancel that! I found the issue. There was some rogue code further down where for some reason under textbox1 change there was code saying TextBox1.Text = ListBox1.Value.....(!) Sorry for wasting your time! But one further question, will using the method above result in large amount of data being put in the memory when I have over 500 rows of data being called?