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?
 

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.

Julesdude

Board Regular
Joined
Jan 24, 2010
Messages
197
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
39,044
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
197
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
39,044
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
197
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
39,044
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
197
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
197
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,171,168
Messages
5,874,152
Members
433,029
Latest member
theodoros1

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