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

Julesdude

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

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Julesdude

Board Regular
Joined
Jan 24, 2010
Messages
196
Still in need of an answer. I should mention that the combobox is a form control, not Active X one.
I tried the following code:
Code:
    With kpiws.Shapes("Combobox1").ControlFormat
    .RemoveAllItems
    .AddItem "January"
    .AddItem "February"

But still that didn't work - object doesn't support his property or method on the .removeallitems line. Or if I take that away, the .additem line.

Can anyone help?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Try:
Code:
kpiws.DropDowns("Combobox1").List = Array("January", "February")
 

Julesdude

Board Regular
Joined
Jan 24, 2010
Messages
196

ADVERTISEMENT

Hi Rory

Thanks I gave it a try. But your code gets me the 'Method 'DropDowns' of object _Worksheet failed' error comes up.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Then I don't think it's a Forms combobox - I think it's activex. If you right click it, do you see 'Assign macro' or 'View Code' (or nothing)?
 

Julesdude

Board Regular
Joined
Jan 24, 2010
Messages
196

ADVERTISEMENT

When I right click it, no options appear for me. It just selects the object. Have I been a plumb and put an Active X Control instead?! I didn't mean to. The only way I can get options for it is to select Design Mode and then I can right click and some options appear for me.
Either way, I thought I had tried Active X code as well? As well as referencing using the OLEobject method above, I also tried the following:

kpiws.combobox1.additem
But that didn't work.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,118
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yep, that's ActiveX then. Try declaring kpiws as Object rather than as Worksheet
 

Julesdude

Board Regular
Joined
Jan 24, 2010
Messages
196
Thanks Rory, do I need to change any code when populating the combobox, as well as reference?
 

Julesdude

Board Regular
Joined
Jan 24, 2010
Messages
196
Sorry I still can't get this to work. i took Rory's advice and have now got the following:

Code:
Dim kpiws As Object
Set kpiws = owb.Sheets("SummaryFF,FOTM")
kpiws.Visible = True
kpiws.Activate
With kpiws.OLEObject("Combobox1").Object
    .Clear
    .AddItem "January"
    .AddItem "February"
    .AddItem "March"

also tried:

Code:
With kpiws.Combobox1
.AddItem "2012"

Both DON'T WORK. Think I need to modify the code a bit more now that I've set kpiws as an object. But I don't know how.
 

Forum statistics

Threads
1,136,444
Messages
5,675,894
Members
419,591
Latest member
mersanko

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
Top