ListBox index question ...

[JimP]

To All,

I have a ListBox on a UserForm that works fine ... what I'm trying to
do now is use the .ListIndex in conjunction with offset ... so that I
can assign the adjacent cells contents to a Label's caption:
LblDescription.Caption

I'm stuck trying to convert this index number to an address - with the
hope to then use that address and "offset" 1 cell right ...

Could someone guide me on the correct procedure ...

Thanks,

JimP

''''''''''''''''''''''''''''''''''''''''''

Private Sub ListBox1_Click()
Dim sIndexName As String: sIndexName = ""
' ITEM
With Me.ListBox1
If .ListIndex > -1 Then
sIndexName = .List(.ListIndex)
End If
End With
' followingline is a problem ...
' LblDescription.Caption = ActiveSheet.Range(sIndexName).Address. _
' Offset(0, 1).Value

End Sub

 

[Doug Glancy]

Jim,

I'm not sure I understand what you are trying to do, but this might help.
I'm assuming a UserForm1, ListBox1 and CommandButton1. On Sheet1, cells
A1:A4 are a range named "r_source." I filled it with "a", "b", "c" and "d".
When you run the form and click on the button, it puts the value in the
listbox in the cell in column B.

Private Sub UserForm_Initialize()
With Me.ListBox1
.RowSource = Worksheets("Sheet1").Range("r_source").Address
.ListIndex = 0
End With
End Sub

Private Sub CommandButton1_Click()
With Me.ListBox1
Range("r_source").Cells(.ListIndex + 1, 2) = .Value
End With

End Sub

hth,

Doug Glancy