How do I determine which item has been selected in an Excel ListBox ?

Tocogroup
Tocogroup used Ask the Experts™
on
This relates back to my previous enquiry regarding how to update ListBox items from a range of dates. This was answered successfully by EE expert dlmille

I should have asked the obvious question...how do I determine which of the items in the ListBox was selected by the user ? Also, can I test to ensure they have actually selected an item ?

I want to pass the value (a date) of the selected item to another procedure (a button click) on the same sheet which builds a filename using this date. I've created this button click procedure but not sure if I can pass the date to this procedure.

Toco
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
The answer depends on which type of Listbox you added, since Excel supports several.

Assuming you have an ActiveX listbox, and some code to work with, you can check

The selected item's index (0-based)

If it's -1, nothing is selected; if it's 0, the first item in the list is selected, etc.

Commented:
sorry...the code would be something like this:

If ListBox1.ListIndex =-1 Then
   MsgBox "Nothing is selected"
Else
  Msgbox "You selected item number " & (ListBox1.ListIndex + 1)
End If

Most Valuable Expert 2012
Top Expert 2012
Commented:
Here's some code to help with that:

in a public module:
 
Sub checkLBSelected()
Dim lb As Variant

    Set lb = ActiveSheet.OLEObjects("ListBox1").Object
    
    If IsNull(lb.Value) Then
        MsgBox "Nothing was selected"
    Else
        MsgBox "You selected " & lb.Value
    End If

    MsgBox "There are: " & lb.ListCount & " items in the list"
    
End Sub

Open in new window


in the worksheet codepage:
Private Sub ListBox1_Click()
    MsgBox "You selected " & ListBox1.Value
End Sub

Open in new window


See attached,

Dave
ActiveX-LB-Add-r1.xls
NorieAnalyst Assistant

Commented:
You can just check the Listbox ListIndex property, if it's -1 then nothing has been selected.

If it's anything else something has been selected and you can get the item using ListBox1.Value.

That's for a single select listbox of course.

Author

Commented:
That was very useful Dave. I plugged your code into my Save_Workbook procedure and it takes the selected date item and uses it in the filename.
Many thanks for your help
Toco

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial