Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get the index in VBA of the selected item in a ListBox
I'd like to get the index of the selected item in a ListBox in VBA.
In real VB I do it with the following code: Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Private Sub UserForm_Initialize() Dim Counter As Long ListBox1.Clear For Counter = 1 To 20 ListBox1.AddItem "MyItem " & Counter Next CommandButton1.Caption = "Index of selected Item" End Sub Private Sub CommandButton1_Click() MsgBox (SendMessage(ListBox1.hWnd, &H19F, 0, 0)) End Sub But how can I do it in VBA (e.g. Excel)? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get the index in VBA of the selected item in a ListBox
Just use the ListIndex property of the ListBox control. It will be -1 if no
item is selected. It is the same as real VB. Why do you jump through the hoops with SendMessage in the first place? With Me.ListBox1 If .ListIndex < 0 Then Debug.Print "No item selected" Else Debug.Print "Item: " & CStr(.ListIndex), .List(.ListIndex) End If End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Stefan Mueller" wrote in message ups.com... I'd like to get the index of the selected item in a ListBox in VBA. In real VB I do it with the following code: Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Private Sub UserForm_Initialize() Dim Counter As Long ListBox1.Clear For Counter = 1 To 20 ListBox1.AddItem "MyItem " & Counter Next CommandButton1.Caption = "Index of selected Item" End Sub Private Sub CommandButton1_Click() MsgBox (SendMessage(ListBox1.hWnd, &H19F, 0, 0)) End Sub But how can I do it in VBA (e.g. Excel)? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get the index in VBA of the selected item in a ListBox
chip:
what would be the difference in using either of these? they both seem to return the same result. ?.listbox1.List(.listbox1.ListIndex)) ?.listbox1.Value -- Gary "Chip Pearson" wrote in message ... Just use the ListIndex property of the ListBox control. It will be -1 if no item is selected. It is the same as real VB. Why do you jump through the hoops with SendMessage in the first place? With Me.ListBox1 If .ListIndex < 0 Then Debug.Print "No item selected" Else Debug.Print "Item: " & CStr(.ListIndex), .List(.ListIndex) End If End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Stefan Mueller" wrote in message ups.com... I'd like to get the index of the selected item in a ListBox in VBA. In real VB I do it with the following code: Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Private Sub UserForm_Initialize() Dim Counter As Long ListBox1.Clear For Counter = 1 To 20 ListBox1.AddItem "MyItem " & Counter Next CommandButton1.Caption = "Index of selected Item" End Sub Private Sub CommandButton1_Click() MsgBox (SendMessage(ListBox1.hWnd, &H19F, 0, 0)) End Sub But how can I do it in VBA (e.g. Excel)? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get the index in VBA of the selected item in a ListBox
There is no difference in the result. The former is most useful when
retrieving a secondary column from the list. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... chip: what would be the difference in using either of these? they both seem to return the same result. ?.listbox1.List(.listbox1.ListIndex)) ?.listbox1.Value -- Gary "Chip Pearson" wrote in message ... Just use the ListIndex property of the ListBox control. It will be -1 if no item is selected. It is the same as real VB. Why do you jump through the hoops with SendMessage in the first place? With Me.ListBox1 If .ListIndex < 0 Then Debug.Print "No item selected" Else Debug.Print "Item: " & CStr(.ListIndex), .List(.ListIndex) End If End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Stefan Mueller" wrote in message ups.com... I'd like to get the index of the selected item in a ListBox in VBA. In real VB I do it with the following code: Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Private Sub UserForm_Initialize() Dim Counter As Long ListBox1.Clear For Counter = 1 To 20 ListBox1.AddItem "MyItem " & Counter Next CommandButton1.Caption = "Index of selected Item" End Sub Private Sub CommandButton1_Click() MsgBox (SendMessage(ListBox1.hWnd, &H19F, 0, 0)) End Sub But how can I do it in VBA (e.g. Excel)? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get the index in VBA of the selected item in a ListBox
ok, i see.
thanks, bob -- Gary "Bob Phillips" wrote in message ... There is no difference in the result. The former is most useful when retrieving a secondary column from the list. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... chip: what would be the difference in using either of these? they both seem to return the same result. ?.listbox1.List(.listbox1.ListIndex)) ?.listbox1.Value -- Gary "Chip Pearson" wrote in message ... Just use the ListIndex property of the ListBox control. It will be -1 if no item is selected. It is the same as real VB. Why do you jump through the hoops with SendMessage in the first place? With Me.ListBox1 If .ListIndex < 0 Then Debug.Print "No item selected" Else Debug.Print "Item: " & CStr(.ListIndex), .List(.ListIndex) End If End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Stefan Mueller" wrote in message ups.com... I'd like to get the index of the selected item in a ListBox in VBA. In real VB I do it with the following code: Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Private Sub UserForm_Initialize() Dim Counter As Long ListBox1.Clear For Counter = 1 To 20 ListBox1.AddItem "MyItem " & Counter Next CommandButton1.Caption = "Index of selected Item" End Sub Private Sub CommandButton1_Click() MsgBox (SendMessage(ListBox1.hWnd, &H19F, 0, 0)) End Sub But how can I do it in VBA (e.g. Excel)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need selected item from listbox after double click | Excel Programming | |||
Add item to listbox, but only if it is not already there. | Excel Programming | |||
How to copy selected listbox item to sheet | Excel Programming | |||
Listbox - write newly selected item back to the worksheet | Excel Programming | |||
Count number of selected item in Listbox | Excel Programming |