Can't add items to combobox on worksheet in another workbook

[Julesdude]

Hi there all,
I have a macro that allows the user to search pick and load other workbooks held on the network. Once the other workbook is loaded, you get a front sheet showing a dashboard of figures and two combobox - one showing month and then one showing year so that the user can display different data.

My problem is that when that other workbook is loaded, both combobox are empty. They do populate as the comboboxes are filled when the worksheet is activated (worksheet activate sub) but of course this only happens if you manually select another sheet and then select to said sheet.

I have tried two options and both have failed. The first was to reference the comboboxes and populate them from the macro running workbook - so populating a combobox on a worksheet in another workbook. The other method involves code within the workbook that actually has the comboboxes, but because this is a template people are submitting, I can't change that quickly or I'll have to make x amount of changes for each of the workbooks. Better to have the macro running workbook send something to the comboboxes in the opened workbook. But how do I do this? At the moment the following brings back an error - 'Unable to get object property of OLEObject class'

Set owb = Workbooks.Open(strRootFolder & Me.ListBox1 & "\" & Me.ListBox2, , True, , , , True, , , , , , False)
kpiws.Visible = True
kpiws.Activate
    With ActiveSheet.Combobox1
    .Clear
    .AddItem "January"
    .AddItem "February"
    .AddItem "March"
End with

This seemed to work in Excel 2010. I ran into probs with 2007. I also tried using OLEObject too, something like:
activesheet.OLEObjects("Combobox1").object.additem "*"

But that brought up the same error. Can anyone please help me?

 

[Norie]

You could put code to populate in the workbook open event of the workbooks with the combobox's.

 

[Julesdude]

That's what I should have done at the beginning. I didn't know that a worksheet_activate sub would not always add items to the combobox. In order for it to do so you need to select another sheet, and then select back into the combobox sheet in order for it to load. It doesn't load just by being opened and visible. And it doesn't load even if you use a worksheet.activate command in the workbook that opens it.