Microsoft Excel - Populate a ComboBox during Worksheet_Open event

I'm working with an .xlsm file using Excel 2007 under Windows XP.  I have a ComboBox that I added to one worksheet that I'm trying to populate using data from 4 cells in another worksheet in the same workbook.  The ComboBox is an ActiveX form (ComboBox1).  For my code I have the following:

Private Sub Workbook_Open()

<<a few non-related things going on here>>

Worksheets("Log").OLEObjects("ComboBox1").Object.ListFillRange = _
   "Events!A" & Worksheets("Events").Ranage("N1").Value + 6 & ":A" & Worksheets("Events").Range("N1").Value + 9

However, whenever it hits the line above, I get a Run-time error '1004' - Unable to get the Object property of the OLEObject class.  Can anyone tell me what I need to add (or get rid of) in order to make this work?

Many thanks.

You were close:

Worksheets("Log").OLEObjects("ComboBox1").ListFillRange = _
   "Events!A" & Worksheets("Events").Ranage("N1").Value + 6 & ":A" & Worksheets("Events").Range("N1").Value + 9

worked for me, without the .object in the middle.  To figure this I put this code in:

    Dim objtest As OLEObject

    Set objtest = Worksheets("Issues").OLEObjects("ComboBox1")

    objtest.ListFillRange = "B3:B29"

In debug mode, once objtest was assigned, I could look at the properties using the Locals window and the ListFillRange was just right off the object level.

Well, shoot.  Here's what I did (the DIM statement was added, too):

Set Years = Worksheets("Log").OLEObjects("ComboBox1")
Years.ListFillRange = "Events!A" & Worksheets("Events").Range("N1").Value + 6 & ":A" & _
    Worksheets("Events").Range("N1").Value + 9

However, when the workbook was opened, I got a "Run-time error '-2147467259 (80004005)' - Method 'ListFillRange' of object '_OLEObject' failed" when it hit the Years.ListFillRange line.  Should I assign the range text to a variable and then let Years.ListFillRange equal that variable?

Hmmm, it is still working for me (once I put the right sheet name down for the combobox).  I just changed the names and pointers from your code to match my test sheet, and it worked ok.  You might try splitting the range address out to a separate piece as well, so you can see what it is resolving to.  Or, just copy the code into the immediate window and ? it out.

When you View, Locals Window, do you see the Years object after it has been assigned, and does it have the ListFillRange property?  You can actually type a value in there to assign the list, for testing purposes.

It might be something as simple as having the value in Issues!N1 as a text instead of a number.

I think I may have narrowed it down a bit.  I tried adding a line to just activate the Combo Box and I got this error:

   Run-time error '1004':

   Cannot start the source application for this object

It appears my problem isn't that it can't fill the combo box, but that it can't do anything with it.  This is an Active X control I added using the Insert button on the Developer tab; what source application is it talking about and why would it not be able to start it?

***BTW:  I just realized that my subject says "Worksheet_Open" when, in fact, this code is actually in the "Workbook_Open" event.  If this makes a difference, I profusely apologize.  When I used the code in the "Worksheet_Activate" event, it works fine.

Just a couple of things to check, is the sheet the combobox is on protected?  Or, is the sheet/cell that the combobox is linked to protected?

I have seen problems with Worksheet_BeforeSave events before, it is possible there is a problem with the Workbook_Open event, though I doubt it.  You can test that by moving the code block to a test sub procedure on the worksheet the combobox resides on, or to a new module, and running it from there.

I appreciate all your help.  I decided to approach this a different way, however.  Since the code works fine when I use it with the Worksheet_Activate event, I just moved it there, tweaking it just a bit so that it doesn't fire every time I activate the sheet.  It's working fine that way, now.  Plus, my head was getting a little too sore from banging it on my desk.

Many thanks.