VBA Excel ListBOxes Select Case

[dprayner]

Good morning people.

I have a ListBox on my spreadsheet and I am try to test what the user selects (single or multi item). I want to execute statements based on the selections using a Select Case structure. How is this usually done? DAVE

 

[rmikesmith]

Dave,

It's not clear what you are trying to achieve here. Can you add more detail. Generally, with a multiselect ListBox, your code loops through the .Selected() array to determine whether each entry has been selected by the user. So you would use a setup something like:

For i = 0 To ListBox1.ListCount - 1
  If ListBox1.Selected(i) Then
    'Do something here
  End If
Next i

Regards,
Mike

 

[rmikesmith]

FYI. The syntax for working with a ListBox on a worksheet will be a little different than what I posted (which is for a ListBox on a Userform).


Mike

 

[dprayner]

Hi people. Lets say I have a ListBox on a Excel form. The list consists of fruits: Apple, Bananas, Grapes, Plums, and Oranges. I want to executes statements based on what the user clicks on. Would I use the click event or the change event? Also the ListBox is set for multiple selections. Also if a user unclicks a selection, I want to reverse the code like making something visible when clicked, and invisible when unclicked. DAVE

 

[rmikesmith]

Dave,

Using the info in your last entry, I set up the following demo.

Created a Userform to which I added a Listbox (ListBox1). Set these properties: Multiselect = fmMultiSelectMulti; ListStyle = fmListStyleOption (adds a checkbox to each entry). The ListBox is populated with the fruit names indicated. Five Label controls added to the Userform, one for each fruit. Set captions to the names of the fruits. Set Names to Label0 .. Label4. Set Visible property to False. Create the Change event procedure for the ListBox to look like

Private Sub ListBox1_Change()
Dim i As Long
   With ListBox1
     For i = 0 To .ListCount - 1
       Me.Controls("Label" & i).Visible = .Selected(i)
     Next i
   End With
End Sub

Display the Userform and select one or more fruit entries. The corresponding label will change visibility depending on the items selected.

This demonstrates how you would code actions based on the Listbox selection. As you can see, it's a little trickier when working with a multiselect Listbox, since you need to loop through the entries.

Hope this helps,
Mike

 

[dprayner]

Hi. Sorry I don't know why I said form, I meant Excel spreadsheet. DAVE

 

[rmikesmith]

OK, The basics remain the same, except your ListBox and Labels are located on a worksheet. The ListBox1_Change procedure will now be located in the Sheet's class module (double-click on the Listbox to generate the procedure shell). The procedure will now look like this:

Private Sub ListBox1_Change()
Dim i As Long
   With ListBox1
     For i = 0 To .ListCount - 1
       Me.OLEObjects("Label" & i).Visible = .Selected(i)
     Next i
   End With
End Sub

Mike

 

[dprayner]

Hi. What if I wanted to make tabs visible (ie. Banana tab, apple tab, etc.)? Also make them invisible when they are "unclicked". DAVE

 

[rmikesmith]

Do you mean worksheet tabs? If so, the sheets themselves will need to be hidden since you cannot selectively "hide" tabs (which is a workbook-wide property).


Mike

 

[dprayner]

Yes worksheet tabs. Any suggestions? DAVE

 

[rmikesmith]

Try this:

Private Sub ListBox2_Change()
Dim i As Long
   On Error Resume Next
   With ListBox1
     For i = 0 To .ListCount - 1
       If .Selected(i) Then
         Worksheets(.List(i)).Visible = xlSheetVisible
       Else
         Worksheets(.List(i)).Visible = xlSheetHidden
       End If
     Next i
   End With
End Sub

Regards,
Mike