ActiveX Combobox ListFillRange

I have an ActiveX ComboBox on sheet 2 which I am trying to populate with a dynamic named range on sheet 6. The dynamic named range has 2 columns both of which I wish to see in the combobox list. I have tried various suggestions from previous posts, couple of examples below, but I am missing something. The named range is not part of a table and being defined using the Offset function

=OFFSET('Implement Defaults'!$B$10,0,0,COUNTA('Implement Defaults'!C2:C9999),2)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Any ideas where I am going wrong

Originally Posted by rabbit_post

I have an ActiveX ComboBox on sheet 2 which I am trying to populate with a dynamic named range on sheet 6. The dynamic named range has 2 columns both of which I wish to see in the combobox list. I have tried various suggestions from previous posts, couple of examples below, but I am missing something. The named range is not part of a table and being defined using the Offset function

=OFFSET('Implement Defaults'!$B$10,0,0,COUNTA('Implement Defaults'!C2:C9999),2)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Any ideas where I am going wrong

could be column widths need to be defined.

Please Login or Register  to view this content.

or whatever width you want
And i would delete this line

Please Login or Register  to view this content.

Since ListFillRange is a parameter in the properties, it will reflect whatever the last value it was given and does not need to be cleared beforehand.

Please Login or Register  to view this content.

The .Address is correct, but I am not so sure about the '(External = True)' part. Don't know if the Properties class is set up to accept that.

Hi all- ListFillRange requires an address or the name of a named range as a string, so:

Please Login or Register  to view this content.

implementList is assumed to have workbook scope.

My post #2 was edited to show this for the listfillrange.

Please Login or Register  to view this content.

Since your fill range is on a different sheet than your combobox, you will need the sheet reference and the cell range address in A1 formst. The second = symbol inside the quote marks is not needed.

Please Login or Register  to view this content.

I think that one should do it.

Why not something like this.
You can put either this in Code Module of Sheet2

Please Login or Register  to view this content.

or this in Code Module of Sheet6

Please Login or Register  to view this content.