Populating Combo Box on WorkBook Open

[wtjessup]

Is there any way to populate a combo box with a few items in a worksheet when the workbook is opened up? So far I can not get this to work with the WorkbookOpen() event.

Thanks in advance

 

[Norie]

What type of combobox is it?

Is it from the Forms or Controls Toolbox?

How have you tried to populate it?

 

[wtjessup]

The combo box is from the controls toolbox and it is on the worksheet, not in a userform. I believe I can eventually figure out how to have them select one of the items and other actions be performed, but I just cant seem to get the list populated.

 

[Norie]

What do you want to populate it with?

What code have you already tried?

If it's on a worksheet you should probably use the sheet's activate event to populate it.

 

[Tom Urtis]

Using the Open event would be better, here is the general syntax for an example, modify for parent sheet name, combobox name, and the "few items" you want to populate the combobox with.

Private Sub Workbook_Open()
With Worksheets("Sheet1").OLEObjects("ComboBox1").Object
.Clear
.AddItem "Value 1"
.AddItem "Value 2"
.AddItem "Value 3"
.ListIndex = 0 'optional
End With
End Sub


Make sure the ListFillRange property of the combobox is empty.

Place the code in your workbook module and see if it accomplishes what you are after. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the above procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.

After that, save the workbook, close it, and when you reopen it the values will be populated into the combobox as the code suggests.

 

[wtjessup]

Thanks for those suggestions, I see where I wasnt putting the code in the right place (Worksheet instead of Workbook).