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!