Filling a combo box via macro

[TheNavigator]

First time poster on these forums, thanks for the help in advance!

Ultimately what I want to do is create a macro that decides how big a range is (it will always begin at B2), and set the combo box's input range as the said range. In this case, I've split the task into two methods, one that gets the range I want, and the other that sets up the combo box.

I am pretty confident the first method will work, but I keep getting a "Variable not defined" error for "ArmsDropDown" when I try to run the HICSInitialize macro. I've named the combo box on the worksheet "ArmsDropDown" (that is how it appears when selected). I am assuming that I'm neglecting to do something very obvious, since all the solutions I've found by searching are very straight-forward, but still fail for me.

Please Login or Register  to view this content.

As a side note, I am new to programming in VBA for excel, but not new to programming in general. If you can make any suggestions about how this can be done better or more efficiently, I'd like to learn.

[rylo]

Hi

This assumes that ArmsDropDown is on sheet Arms.

Please Login or Register  to view this content.

If that is not the case, then can you advise where the object is, and what sort of object it is.

rylo

[TheNavigator]

Okay, I tried adapting your code to mine:

Please Login or Register  to view this content.

But now I get an error:

Runtime error '91':
Object variable or With block variable not set

With emphasis on the line:

Please Login or Register  to view this content.

If that is not the case, then can you advise where the object is, and what sort of object it is.

ArmsDropDown is a combo box created through the forms toolbar, and is located on sheet "Main". It's never declared in my code, but exists and works perfectly fine on the spreadsheet. I just want to create a macro to automatically give it a range, since the data on the sheet it pulls information from, "Arms", is subject to change.

[rylo]

Hi

You left off the SET.

Please Login or Register  to view this content.

rylo

[TheNavigator]

After using the help you gave me and searching around a little more, I got it working with this:

Please Login or Register  to view this content.

Just thought I'd post the solution. Thanks, rylo. =)

[JnWells]

Thanks for posting your solution.
I would much appreciate a copy of the workbook so I can figure it out in more complete context.