Loading Sheet Names into Combo Box

[ExcelMonkey]

I have four worksheets. The first sheet ("Inputs") has a combo bo
which loads the sheets names of all the sheets in the workbook. Th
remaining three sheets are called "Case 1", "Case 2", and "Case 3".

The problem is that the code below loads the sheet names properly but:

1) I do not want the input sheet included in the list
2) after the click event, the chosen item from the list does not sta
loaded in the combo box

What is wrong with this?

Thanks

Private Sub ComboBox1_Click()
Dim Sh As Worksheet

With ComboBox1
.Clear
For Each Sh In ActiveWorkbook.Sheets
.AddItem Sh.Name
Next
End With

End Su

 

[Tom Ogilvy]

Private Sub ComboBox1_Click()
Dim Sh As Worksheet

With ComboBox1
Clear
For Each Sh In ActiveWorkbook.Sheets
if sh.Name <> "Inputs" then
AddItem Sh.Name
End if
Next
End With
End Sub

Not sure what you mean by doesn't stay loaded. If you mean it doesn't
appear in the "textbox". What click event? What other event code do you
have?

 

[ExcelMonkey]

So Tom, when I use this code, the combo box pulls the sheets into th
list as I click onto the combo box button. Afer the click i
completed, the chosen item from the list does not stay show up in th
box. That is, it loads it into the list, allow me to pick it, but doe
display the chosen item int eh box after the click.

Is click the right event? Or should it be a Change event?




'This loads the ComboBox list
Private Sub ComboBox1_Click()
Dim Sh As Worksheet

With ComboBox1
.Clear
For Each Sh In ActiveWorkbook.Sheets
If Sh.Name <> "Inputs" Then
.AddItem Sh.Name
End If
Next
End With
End Su

 

[Tom Ogilvy]

I look at your code and saw CommandButton1_click rather than
Combobox1_click - my inattention.

The reason it doesn't stay loaded as you say, it because you rebuild the
list on the click event. When the list is rebuilt, the selection is
removed.

'This loads the ComboBox list
Private Sub ComboBox1_Click()
Dim Sh As Worksheet
Dim sVal as String
With ComboBox1
sVal = .Text
Clear
For Each Sh In ActiveWorkbook.Sheets
If Sh.Name <> "Inputs" Then
AddItem Sh.Name
End If
Next
.Value = sVal
End With
End Sub

Should do it. Click event would be proper. I assume you are rebuilding
the list because there might be sheets added.

 

[ExcelMonkey]

Thanks. thats grea

 

[ExcelMonkey]

So that works fine Tom. Now here is my next question. Suppose I no
want the result of the combo box to trigger another routine tha
selects that sheet that is chosen. That is I use the routine below t
load sheet names into a combo box. I now want this to trigger anothe
routine that selects the sheet.

I am not sure where to put this routine. I know that it has to go i
the sheet module. Do I pass the sheet names into an array and the
pass this array to another routine?

And lastly, I do not want this to run continuously. That is I want t
be able to pick a sheet name with the combo box, have it go to tha
sheet ONLY after the name has been chosen in the box. I guess I hav
to use the right "event" to make sure that the routine does not ru
continuously. Would this be the Change event?

How would you do this?

Thanks



'This loads the ComboBox list
Private Sub ComboBox1_Click()
Dim Sh As Worksheet
Dim sVal As String

With ComboBox1
sVal = .Text
.Clear
For Each Sh In ActiveWorkbook.Sheets
If Sh.Name <> "Inputs" Then
.AddItem Sh.Name
End If
Next
.Value = sVal
End With

End Su

 

[Tom Ogilvy]

'This loads the ComboBox list
Private Sub ComboBox1_Click()
Dim Sh As Worksheet
Dim sVal as String
With ComboBox1
sVal = .Text
Clear
For Each Sh In ActiveWorkbook.Sheets
If Sh.Name <> "Inputs" Then
AddItem Sh.Name
End If
Next
.Value = sVal
Worksheets(.Value).Activate
End With
End Sub

The click event is appropriate