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)?

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)?

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)?

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)?

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)?