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

Julesdude

Board Regular
Joined
Jan 24, 2010
Messages
197
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'


Code:
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?
 
When I run the code and it opens the Penine Foods workbook everything works OK, no errors and the combobox's are populated.
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
It sounds like macros are disabled in the workbook you are opening so it opens in Design Mode. If you enter:
Code:
?Application.AutomationSecurity
in the immediate window, what do you get?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Code:
Application.Automationsecurity = msoautomationsecuritylow
in your code just before you open the workbook.
 
Upvote 0
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?
 
Upvote 0
It means that macros are allowed for any workbooks opened by code. You should reset it at the end:
Code:
Application.Automationsecurity = msoautomationsecuritybyUI
in your case.
 
Upvote 0
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?
 
Upvote 0
Typically, yes - although I think it differs in some versions, and it can be altered through a Group Policy.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top