Jason points out a problem with setting the Multiselect property of a Listbox using VBA, as opposed to setting it manually at design time. Take this code:
Private Sub UserForm_Initialize()
Dim sht As Worksheet
Me.lstSheets.MultiSelect = 2
For Each sht In ActiveWorkbook.Worksheets
Me.lstSheets.AddItem sht.Name
Next sht
End Sub
Dim sht As Worksheet
Me.lstSheets.MultiSelect = 2
For Each sht In ActiveWorkbook.Worksheets
Me.lstSheets.AddItem sht.Name
Next sht
End Sub
It produces this userform with unsightly rectangles around each list entry.
This happens with MultiSelect = 1 or 2, but not zero. If you must change this property in code (and Jason does, but I’ve simplified his situation for this example), do it after you populate the control.
Private Sub UserForm_Initialize()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
Me.lstSheets.AddItem sht.Name
Next sht
Me.lstSheets.MultiSelect = 2
End Sub
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
Me.lstSheets.AddItem sht.Name
Next sht
Me.lstSheets.MultiSelect = 2
End Sub
Good question, Jason. I rarely set that property at run time, but if I do, I can’t have my Listboxes all rectangly.
“No definitions were found for rectangly.” Google Dictionary Search
Evidence that English continues to evolve. You knew what he meant, didn’t you?
Rectangly means to make object rectangly with rectangly
lines. This is from my dictionary
“No definitions were found for rectangly.” Google Dictionary Search
Don’t need Google or Dictionary to know what Dick meant.
Language does evolve; a guy at work asked a large group of people if RFK stadium in D.C. was “Metroable”; and we all knew what he meant. You could say that if the communication is successful, then it is a word!
Anywho, thanks much to Dick for the help. Soon I’ll be able to publish my little tool!
I am interested in using a userform like the one in this example. I would like to add code such that when the CommandButton1 is clicked the code would take the user to the worksheet selected in the listbox. Any suggestions?
Thanks