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?

 

[RoryA]

We always need more information than "doesn't work".

If you get errors, what are they and on which lines? The first code sample should use OLEObjects, not OLEObject

 

[Julesdude]

Sorry. Basically the code I just quoted is now being used. I get a 'Object doesn't support this property or method' error message on the line With kpiws.OLEObject("Combobox1").Object

 

[RoryA]

Like I said, that should be OLEObjects, not OLEObject.

 

[Julesdude]

I get the same error using OLEobjects

 

[RoryA]

On the same line? I don't understand that at all.

 

[Norie]

I've set up a workbook, ComboTest.xlsm, with an ActiveX combobox called ComboBox1 on a sheet called SummaryFF,FOTM.

When I run this code, from another workbook, the workbook is opened and the combobox is populated.

Sub FillCombo()
Dim owb As Workbook
Dim kpiws As Worksheet

    Set owb = Workbooks.Open("C:\Test\ComboTest.xlsm")
    Set kpiws = owb.Sheets("SummaryFF,FOTM")
    
    With kpiws.OLEObjects("Combobox1").Object
        .Clear
        .AddItem "January"
        .AddItem "February"
        .AddItem "March"
    End With
    
End Sub

Is there anything I missing in the setup? Names? Protection?

 

[Julesdude]

Thanks Norie and Rory,

The only thing I could think of that might be interfering was the fact that I have both Excel 2007 and 2010 open. I closed them both down completely and started again. But i got the error 'Unable to get the Object property of the OLEObject class' on the line:

With kpiws.OLEObjects("Combobox1").Object

My code in full is:

Dim kpiws As WorksheetApplication.EnableEvents = False
Label1.Caption = "Opening KPIs..."
DoEvents
Set owb = Workbooks.Open(strRootFolder & Me.ListBox1 & "\" & Me.ListBox2, , True, , , , True, , , , , , False)
    If area = "Fresh Foods/FOTM" Then
    Set kpiws = owb.Sheets("SummaryFF,FOTM")
    kpiws.Range("SAllergyDate").Font.Size = 11
    
    ElseIf area = "Ambient" Then
    Set kpiws = owb.Sheets("Summary Ambient")
    End If
    
kpiws.Visible = True
kpiws.Activate

MsgBox owb.Name
MsgBox kpiws.Name
With kpiws.OLEObjects("Combobox1").Object
    .Clear
    .AddItem "January"
    .AddItem "February"
    .AddItem "March"
    .AddItem "April"
    .AddItem "May"
' ...
End with

owb is already defined as a public workbook.
I used a msgbox to pop up the owb.name and kpiws.name. Both are correctly assigned. The workbook opens but then the error message appears.

Would this have anything to do with compatibility? I've got this running on 2007. But the sheet will have been submitted by suppliers that may have Excel 2003. So when they submit back and it is opened here, the original sheet has lost some of its 'fidelity' appearance. I don't know, would the combobox object be changed somehow when run through an older version?

 

[Norie]

Can you upload a sample file to somewhere like Box.net and post a link to it here?

 

[Julesdude]

Just a couple more points to note. When the I come out from the error message, the new workbook is open. I try to select the drop-down combobox there, but it will only select - it won't allow the list to appear. So it's selected and the formula bar reads:
=EMBED("Forms.ComboBox.1","")

Now, if I then quit that new combobox workbook that opened but keep the macro running workbook open (that opens it), and run the routine again, there is no error and the workbook opens fine and the comboboxes in it are loaded successfully.

So it runs the second time but not the first?!?! I really don't understand. Obviously it is not good that the first time it is run it doesn't work.

 

[Julesdude]

Hi Norrie,
Ok I have uploaded to box.net after stripping certain unimportant things out. Link to download files is:
https://app.box.com/s/0ho62nvp8toz40xtj8qp
https://app.box.com/s/jvvubdeq7qiw9bbd1mpy

You will need to set up a folder to store the workbook that will open, which should be the name of the workbook to be opened, and place the workbook in there as it's named. You will need to change the strRootFolder variable to reflect the path to this folder, and end it with a '\'