Populate textbox with cell value selected in listbox in a different column

[oteixeira62]

Hello to all,

I’m trying to adapt the VBA code to populate the userform TextBox2 with a value located in a non-contiguous, different column, as explained in the picture bellow.

This is the code I'm using:

Option Explicit
Private Sub ListBox1_Click()
    Dim R As Integer
    If Me.ListBox1.ListIndex = -1 Then    'not selected
    ElseIf Me.ListBox1.ListIndex >= 0 Then    'User has selected
        R = Me.ListBox1.ListIndex
        With Me
            .TextBox1.Value = ListBox1.List(R, 0)
        End With
    End If
End Sub
Private Sub UserForm_Initialize()
    Dim a, Q&, i&, R&, b
    a = Range("'Tabelas'!A1").CurrentRegion: Q = UBound(a)
    ReDim b(1 To Q)
For i = 2 To Q
  If a(i, 4) = "A" Then R = 1 + R: b(R) = a(i, 1)
Next
ListBox1.Clear
    If R > 0 Then
       ReDim Preserve b(1 To R)
    ListBox1.List = b
End If
End Sub

Many thanks for any kind help.

Octavio

[#2 · Accepted solution]

@oteixeira62 

Try this version of ListBox1_Click:

Private Sub ListBox1_Click()
    Dim s As String
    If Me.ListBox1.ListIndex >= 0 Then    'User has selected
        s = Application.VLookup(Me.ListBox1.Value, Worksheets("Tabelas").Range("A:H"), 8, False)
        Me.TextBox1.Value = s
    End If
End Sub

[#3]

One again Hans, Your help is precious,
Many thanks!
Octavio