CODEWITHSUNDEEP
excelvba
Bimmie
Bimmie

Reputation: 31

Listbox in Excel - How to determine no selection

I have an Excel spreadsheet with a listbox on sheet1 that populates from a named group on sheet2. There are 4 entries in this named group.

I want the user to make a selection from this listbox (1 column) before they do anything else. I'm trying to code to check for a valid selection from the listbox but, TopIndex = 0, and .Value, .Selection, .Selected either don't work or they return 0 but 0 is the index for the first entry in the listbox so it's like I always make a selection.

If I check for Listbox.value <> "" it returns null whether or not I make a selection.

I've searched the internet all night looking for a solution and keep coming up empty handed.

I'm stuck. Looking for suggestions.

Upvotes: 1

Views: 10399

Answers (3)

Ahmad Dandis
Ahmad Dandis

Reputation: 1

You need to count the selected items, then make the condition:

For a = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(a) = True Then
    Numselections = Numselections + 1
End If
Next a
If Numselections < 1 Then
MsgBox ("Please select item!")
Exit Sub
End If

Upvotes: 0

Bimmie
Bimmie

Reputation: 31

Not sure why it didn't work for me but when I used

listbox1.listindex I kept getting 0

So, I changed how I populated my listbox by using

Private Sub Workbook_Open() ' Add site names to listbox With Sheet1.ListBox1 .AddItem "Hayward" .AddItem "Exeland" .AddItem "StoneLake" .AddItem "Winter" End With End Sub

Later on, when I coded to define the file I needed to open, I used

x = Sheet1.ListBox1.ListIndex

Select Case x
    Case 0
        sSite = "Hayward"
    Case 1
        sSite = "Exeland"
    Case 2
        sSite = "StoneLake"
    Case 3
        sSite = "Winter"
    Case Else
        MsgBox "You MUST select a Site Location", vbOKOnly
        GetTargetFile = "NoSite"
        Exit Function
End Select

GetTargetFile = sSite & sMonth & Yr & ".xlsx"

Now, Listbox1.ListIndex will return -1 if no selection made.

I think my original problem was in how I was trying to populate my listbox in that no matter what I did, both the FIRST and NO SELECTION returned 0.

Thanks for responding!

Upvotes: 0

Storax
Storax

Reputation: 12207

You are possibly looking for this piece of code

If ListBox1.ListIndex = -1 Then
    MsgBox "Nothing selected"
Else
    MsgBox "Selected: " & ListBox1.ListIndex
End If

Listindex is equal -1 if nothing is selected in the listbox. Otherwise it is the index of the selected element starting with 0.

The above code works for a listbox where multiselect is false.

For a listbox with "multiselection" on this piece of code might probably help you

Dim i As Long

 For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        MsgBox "Selected: " & i
    End If
Next i

Upvotes: 2

Related Questions