Populate combobox with headings from named range

[davidb50]

Hi
I'm trying to populate a combo box with the headings from a named range. The code runs but no values appear in the list. Would appreciate it if someone could tell me what I'm doing wrong.

Private Sub cmbHeadings_Change()    
'================================================================================
'Macro purpose: Populates a combobox with the headers from a named range
'Created by:
'================================================================================
    
    Dim MyRange As Range
    Dim i As Integer
    
    Set MyRange = Range("Lists_StoreName_All") '.Offset(-1, 0)
    
    With Me.cmbHeadings
        .Clear
        For i = 1 To MyRange.Columns.Count
            .AddItem MyRange.Cell(1, i).Value
        Next i
    End With
    
End Sub

 

[Andrew Poulsom]

In this line:

.AddItem MyRange.Cell(1, i).Value

Cell should be Cells.

 

[davidb50]

Yes it should! Thank you. However, I've changed that and still not populating the box?

In this line:

.AddItem MyRange.Cell(1, i).Value

Cell should be Cells.

 

[Andrew Poulsom]

What happens if you transfer your code to the UserForm_Initialize event procedure? Your current code will only fire when the user selects something from the ComboBox, which isn't possible when it's empty.

 

[davidb50]

Works perfectly now. Many thanks for your help

What happens if you transfer your code to the UserForm_Initialize event procedure? Your current code will only fire when the user selects something from the ComboBox, which isn't possible when it's empty.