Inserting Data from another sheet based on Listbox Selection

[Borg]

Hi,

This is a further question regarding inserting data from another
worksheet based on data from a Listbox selection.

On Sheet2, I have data in Column A and B. The VBA code will list the
Column B data in the listbox, and upon selection will include the text
(or value) in the active cell selected.

How would I write code to include the corresponding Column A data
related to Column B data from the same row (For example, if I selected
Row 1, Column B from the Listbox, how would I get the related Row 1,
Column A data to be inserted in another cell)?

This is sort of what I'm working on:

Private Sub ListBox1_Click()
ActiveCell = ListBox1.Value
ActiveCell.Offset(0, 2) = ?????? <---(I wanted column A data here.)
End Sub

************************************
Private Sub UserForm_Initialize()

Dim r As Long
ListBox1.RowSource = ""
For r = 1 To 110
ListBox1.AddItem Sheets("Sheet2").Range("B" & r).Value
Next r
End Sub

Thanks!

 

[Die_Another_Day]

ActiveCell.Offset(0,2).Formula = "=Sheet2!A" & ListBox1.ListIndex + 1
the + 1 on the end is to offset the "0" based array for the listindex

Charles Chickering
xl Geek

 

[Borg]

Thank you!