Populate ComboBox with a defined range

[JCK101]

I am lost, after reading oodles of suggestions on forums, I still don't understand why this code does not work:

Code:
Private Sub UserForm_Initialize()
Me.ComboBoxService.List = Worksheets("LookupList").Range("ChartOfAccounts").Value
End Sub

My named range ChartOfAccounts is refers to: =OFFSET(LookupLists!$A$2, 0, 0, COUNTA(LookupLists!$A:$A)-1,1)

I receive: Run time error # 1004 "application-defined or object-defined error"
I searched that too, but could not understand the solutions offered to make it work in my example.

Whereas this code works:

Code:
Private Sub UserForm_Initialize()
Me.ComboBoxService.List = Worksheets("LookupList").Range("a2:a100").Value
End Sub

I prefer to use the dynamic range in case my list grows over 99 records, and to learn to work with very large lists, say 500 or 5000.

Any help is appreciated.

 

[dmt32]

Your example should work - suggest that you check the spelling of your named range "ChartOfAccounts" & ensure that it matches with what you have in names.


Dave

 

[JCK101]

Your example should work - suggest that you check the spelling of your named range "ChartOfAccounts" & ensure that it matches with what you have in names.


Dave

Hi Dave,

Thank you for taking the time to help me.

I have checked and rechecked all names a hundred times. They are correct. From the blogs that I have read like you said, "it should work", but it does not.

When highlighing with red for visibility, I missed highlighting the "s" at the end of ChartOfAccounts. I even did a copy and paste right from the Name Manager, suspecting that I was entering the name wrong, and checked to make sure the name did not have a space at the end.

Again, thank you for your time.

 

[JCK101]

I guess I started celebrating before the game was over, lol. What I thought was running, is "not" running.

Using this statement: Me.ComboBoxService.List = Worksheets("LookupList").Range("a2:a100").Value

The userform works without generating errors, but the ComboBoxService is "not" populated, it is blank. What else I am doing wrong?

 

[dmt32]

The userform works without generating errors, but the ComboBoxService is "not" populated, it is blank. What else I am doing wrong?

I have no idea as your code should work. You could try a different approach - uses bit more code but hopefully, will give desired result:

Private Sub UserForm_Initialize()
    Dim rng As Range
    Dim i As Range
    Set rng = Worksheets("LookupList").Range("ChartOfAccounts")
    With Me.ComboBoxService
        .RowSource = ""
        For Each i In rng
            .AddItem i.Value
        Next i
        If .ListCount > 0 Then .ListIndex = 0
    End With
End Sub

Dave