Populate ComboBox with VBA

[logius]

In Excel there's a combobox that needs to be populated with data from a worksheet (A1->A24). A FOR loop seemed the most obvious way to do it, but when the code runs the compiler spits out an "Invalid Argument" error but doesn't highlight any specific piece of code.

Dim MySheet as Worksheet

Set MySheet = Worksheets("Sheet2")

For i = 1 To 24
    ComboBox1.AddItem MySheet.Range("A1").Offset(i - 1).Value, i
Next i

Once this is working the next step will be to populate a second combobox (ComboBox2) with data (A2->A5, or B2->B5, or C2->C3, etc) based on the selection from the first (ComboBox1). Any ideas as to how this can be accomplished? Any help would be greatly appreciated.

----------------------------------------
If you are reading this, then you have read too far...

 

[SkipVought]

Hi,

Much easier to assign RANGES to the listfillrange.

Is there a reason for using the AddItem method?

Check out the following Spreadsheet functions for defining Dynamic Ranges -- for instance a range based on a selection.

OFFSET
MATCH
COUNTIF
COUNTA

How can I rename a table as it changes size faq68-1331



Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[logius]

No reason, it just happened to be the first function that popped up. Everything seems moot since using RowSource solved the issue.

Many thanks.

----------------------------------------
If you are reading this, then you have read too far...

 

[xlbo]

If you want to know why there was an error - you were missing a ( and a )

MySheet.Range("A1").Offset((i - 1).Value, i)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question