How can i access a listbox?

A

AsIs

Hello all.
I have a listbox on the worksheet (not on a userform).
I set range input and linked cell with properties window.

Q.: How can i access this listbox from workbook macro? For example
determine selected item.

Thanks.

PS: Excel XP
 
T

TroyW

You didn't specify whether the listbox is MultiSelect or not, but the code
below should get you started. The code assumes that ListBox1 is on Sheet1.

Troy


Sub Test1()
Dim lbx1 As MSForms.ListBox
Dim ii As Integer

'''Assumes that ListBox1 is on Sheet1.
Set lbx1 = Sheet1.ListBox1

If lbx1.MultiSelect = fmMultiSelectSingle Then
MsgBox "Value = " & lbx1.Value & _
" : Index = " & lbx1.ListIndex
Else
For ii = 0 To lbx1.ListCount - 1
MsgBox lbx1.List(ii) & _
" : Index = " & ii & _
" : Selected = " & lbx1.Selected(ii)
Next ii
End If
End Sub
 
Top