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)

With Sheet2.ComboBox1
         .ColumnCount = 2
         .ListFillRange = ""
         .ListFillRange = Sheets("Sheet6").Range("implementList").Value
End With

With Sheet2.ComboBox1
         .ColumnCount = 2
         .ListFillRange = ""
         .List = "=Sheet6!" & Sheet6.Range("implementList").Address(Extrernal=True)
End With

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)

With Sheet2.ComboBox1
         .ColumnCount = 2
         .ListFillRange = ""
         .ListFillRange = Sheets("Sheet6").Range("implementList").Value
End With

With Sheet2.ComboBox1
         .ColumnCount = 2
         .ListFillRange = ""
         .List = "=Sheet6!" & Sheet6.Range("implementList").Address(Extrernal=True)
End With

Any ideas where I am going wrong

could be column widths need to be defined.

.ColumnWidths = 50

or whatever width you want
And i would delete this line

.ListFillRange = ""

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.

 .ListFillRange = "=Sheet6!" & Sheet6.Range("implementList").Address

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:

With Sheet2.ComboBox1
         .ColumnCount = 2
         .ListFillRange = ""
         .ListFillRange = "implementList"
End With

implementList is assumed to have workbook scope.

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

.ListFillRange = "=Sheet6!" & Sheet6.Range("implementList").Address

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.

 .ListFillRange = "Sheet6!" & Sheet6.Range("implementList").Address

I think that one should do it.

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

Private Sub Worksheet_Activate()
    Dim sn
    With Sheet6
        sn = .Range("A1", .Range("B" & .Rows.Count).End(xlUp))
    End With
    with ComboBox1
        .ColumnCount = 2
        .List = sn
    End with
End Sub

or this in Code Module of Sheet6

Private Sub Worksheet_Deactivate()
    Dim sn
    sn = Range("A1", Range("B" & Rows.Count).End(xlUp))
    With Sheet2
        With .ComboBox1
            .ColumnCount = 2
            .List = sn
        End With
    End With
End Sub