[RESOLVED] Excel VBA - Auto Load Combo Box

[Hack]

This has got to be the noob question of all noob questions, but I need a combo box preloaded with entries when the excel file opens. Why doesn't this work?

Private Sub Worksheet_Activate()
cboReimbMthd.AddItem "STD"
cboReimbMthd.AddItem "LOUwBD"
cboReimbMthd.AddItem "Model"
End Sub

[anhn]

Load it in Sub Workbook_Open(). Is that the combobox in Sheet1?

Private Sub Workbook_Open()
   With ThisWorkbook.Worksheets(1).cboReimbMthd
      .AddItem "STD"
      .AddItem "LOUwBD"
      .AddItem "Model"
   End With
End Sub

[Hack]

I looked for Open, and couldn't find it....that is why I choose Activate.

I see these possible events:

Activate
BeforeDoubleClick
BeforeRightClick
Calculate
Change
Deactive
FollowHyperLink
PivotTableUpdate
SelectionChange

[si_the_geek]

That's because you are looking at the Sheet events, rather than the Book ones.

[anhn]

Open ThisWorkbook code module instead of Sheet code module.

You can also define a Sub or Function to fill the combo box then call that within Workbook_Open() event.

[koolsid]

looked for Open, and couldn't find it....that is why I choose Activate.

Further to what Anhn suggested, Does this help?

[Hack]

Open ThisWorkbook code module instead of Sheet code module.

You can also define a Sub or Function to fill the combo box then call that within Workbook_Open() event.

I have the sub that does. The problem is that it isn't being called when the file opens.

So, both you and koolsid are saying to do Workbook instead of sheet.

Ok....I will try that. My question now would be do I leave my Public Sub on the Sheet or move it to the Workbook or does it matter?

[koolsid]

you will have to move it in the workbook

[anhn]

1. If the combobox need to fill only once when opening the workbook then you can put code in ThisWorkbook code module as below:

Option Explicit

Private Sub Workbook_Open()
   With Sheet1.cboReimbMthd
      .Clear
      .AddItem "STD"
      .AddItem "LOUwBD"
      .AddItem "Model"
   End With
End Sub

2. If you intend to refill the combo again later then it's better to create a Sub in Sheet1:

Option Explicit

Sub FillComboBox()
   With Me.cboReimbMthd
      .Clear
      .AddItem "STD"
      .AddItem "LOUwBD"
      .AddItem "Model"
   End With
End Sub

And Call it in Workbook_Open(). You should qualify the sub with the sheet codename:

Option Explicit

Private Sub Workbook_Open()
    Sheet1.FillComboBox
End Sub

[Hack]

Thanks guys, it is working fine.