How to reference list box values/text

G

Guest

Guest
How do you reference the text or values in a List Box. i.e. suppose I want to unhide a sheet if a box is checked or highlighted in the list box, how do I reference the value and what function do I use (i.e. ListBox_Change, ListBox_Click, etc...).

For a combo box I would write

Private Sub ComboBox_Change()
If ComboBox = "Argentina" Then
Sheets("Argentina").Visible = True
End If
End Sub

How do I do this same thing with a list box. I want to be able to select multiple countries and unhide the appropriate sheets. Thanks
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Each value/text in a listbox is automatically assigned a number

The first value is 0, second 1, etc.

Use this for what you are trying to do.

Private Sub ListBox_Change()
If SolutionBox.Selected(0) = True Then
Sheets("Argentina").Visible = True
Else
Sheets("Argentina").Visible = False
End If
End Sub
 
Upvote 0
On 2002-03-08 06:49, Anonymous wrote:
How do you reference the text or values in a List Box. i.e. suppose I want to unhide a sheet if a box is checked or highlighted in the list box, how do I reference the value and what function do I use (i.e. ListBox_Change, ListBox_Click, etc...).

For a combo box I would write

Private Sub ComboBox_Change()
If ComboBox = "Argentina" Then
Sheets("Argentina").Visible = True
End If
End Sub

How do I do this same thing with a list box. I want to be able to select multiple countries and unhide the appropriate sheets. Thanks

Something like this should do what you want:

Code:
Private Sub CommandButton1_Click()
    Dim intI As Integer
    
    For intI = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(intI) Then
            Sheets(ListBox1.List(intI)).Visible = True
        End If
    Next intI
End Sub

What I did was fill my ListBox with the names of the sheets in my workbook. Then I added a command button that, when clicked, unhides the sheets that are selected in the ListBox. Like the other Anon poster said, the ListBox items are zero-based. In other words, they start at zero instead of one. This is why you need to use ListBox1.ListCount - 1...if there are 4 sheet names in your list box, we need to go from 0 to 3 instead of from 1 to 4 (but the count tells us there are 4 items in your list).

I hope this helps,

Russell
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top