Load a Combo Box Object upon workbook open

[ExcelMonkey]

I have a combo box which I use to load worksheet names into. However
have noticed that it does not load the names upon opening of th
spreadsheet. That is, when I open the spreadsheet, the box does no
include the entire list. This makes sense since I have used the clic
event to trigger the routine. However when I click on the combo bo
upon opening the spreadsheet it still does not load the list.

I have to go to the code and run it to load the comb box. At thi
point it is fine. Why is this?

Secondly is there a way that I can load the combo box upon opening th
spreadsheet? Teh code for the combo box is in the sheet module and i
a private sub. How do I trigger this private sub upon opening th
spreadsheet. I know I have to use a workbook_open event to tirgger it
But its a private sub routine. Can anyone help me with this?

Thanks


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

With SheetNameCmbBx
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]

In a general sub

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

Then in your current click event
Private Sub Combobox1_Click()
LoadCombobox True
End Sub

Then in the ThisWorkbook module

Private Sub Workbook_Open()
Loadcombobobox False
End Sub