List sheet names in "pop-up" box for users to select
 

Ok, So I figured out how to add and populate a combobox...but the code fires as soon as the selection is made. How do I suspend the code until the OK button is clicked? I thought I was being smart by choosing ComboBox1_DropButtonClick, but that gave me an error!

Here's what I have:

Private Sub ComboBox1_Click()

gsWksTargetName = ComboBox1.List(ComboBox1.ListIndex)
Unload Me

End Sub

Private Sub UserForm_Initialize()

Dim wks As Worksheet
For Each wks In ThisWorkbook.Sheets
If wks.Visible Then ComboBox1.AddItem wks.Name
Next 'wks
Me.Caption = "Select Target"

End Sub

List sheet names in "pop-up" box for users to select
 

Steve submitted this idea :
Ok, So I figured out how to add and populate a combobox...but the code fires
as soon as the selection is made. How do I suspend the code until the OK
button is clicked? I thought I was being smart by choosing
ComboBox1_DropButtonClick, but that gave me an error!

Here's what I have:

Private Sub ComboBox1_Click()

gsWksTargetName = ComboBox1.List(ComboBox1.ListIndex)
Unload Me

End Sub

Private Sub UserForm_Initialize()

Dim wks As Worksheet
For Each wks In ThisWorkbook.Sheets
If wks.Visible Then ComboBox1.AddItem wks.Name
Next 'wks
Me.Caption = "Select Target"

End Sub

Nothing wrong with using the combobox. In fact, given many sheets it's
a better choice since you can cycle through its list by repeated typing
the first letter of the sheetname.

Anyway, I deliberately wrote the code so the form closes immediately
after selection is made, to save user the extra step. Since you've
switched to using a combobox then MOVE the line...

Unload Me

...into the OK button Click event.

====
OT:
Is there a reason why you made new posts to respond to the original
topic, rather than 'replying' to that original post?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

List sheet names in "pop-up" box for users to select
 

Actually, put all the code in ComboBox1_Click into the OK button's
click event. Then delete ComboBox1_Click.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc