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]

When I run the code and it opens the Penine Foods workbook everything works OK, no errors and the combobox's are populated.

 

[RoryA]

It sounds like macros are disabled in the workbook you are opening so it opens in Design Mode. If you enter:

?Application.AutomationSecurity

in the immediate window, what do you get?

 

[Julesdude]

I wish it was working for me :-/
Are you testing on 2007? The odd thing is it works in 2010. But all of my colleagues will be using this on 2007 and it's not working for them - same error etc.

 

[Julesdude]

Rory it says that macros are disabled.
There is a line that I put in - application.enableevents = false
Reason that's there is to prevent the workbook_open macro from starting in the opened workbook.

 

[RoryA]

Which workbook do you have open when you get that message? Try one that doesn't have macros disabled as it's the actual return value I'm interested. You can also try adding:

Application.Automationsecurity = msoautomationsecuritylow

in your code just before you open the workbook.

 

[Julesdude]

They're both open when i get that message but when I'm in the VBE window, the title bar has the name of the opened workbook - so that's why I'm getting that message I think. If I click into the editing bit, the title bar changes to the opener workbook, I type in that command again and I get the number 2. So it seems that the opened workbook has macros disabled.

Ohhhhhhh my goodness. I just tried to put in the Application.Automationsecurity = msoautomationsecuritylow before opening the workbook. AND IT SEEMS TO HAVE WORKED! I have no idea how!

What does this code do and do I need to revert it back to any original setting?

 

[RoryA]

It means that macros are allowed for any workbooks opened by code. You should reset it at the end:

Application.Automationsecurity = msoautomationsecuritybyUI

in your case.

 

[Julesdude]

Well blow me down. Would never have thought to use that in a million years!!!
Just a question - why do I even need this code? Is it not the case that by default macros are enabled in opened workbooks by code?

 

[RoryA]

Typically, yes - although I think it differs in some versions, and it can be altered through a Group Policy.

 

[Julesdude]

Norie and Rory,
This seems to be working a treat now! I seem to have made a rod for my own back as I'm sure there are ways of populating a combobox in the respective workbook rather than doing so from one workbook to another. Even if that's not possible, perhaps in future I should bypass a form based or Active X based combobox altogether and have an in cell drop down (only reason I didn't do this was because you have to zoom in close to the in cell drop down to view the text in the list).
Thanks so much for your help, really appreciated!