Reusing a list of options on a form

Hi

Option 3 looks like the best option as it is working from a named worksheet (ListData)
It comes back with 'Run-Time Error 1004' - 'Unable to get the OLEObjects property of the worksheet class'
I have looked in a number of places including the MS site and can't seem to resolve this
Do you have any thoughts?
Code attached - note your version is currently commented (See UserForm_Initialize - See here section)

Thanks

Well ... that explains it. I (for some reason) thought that the combo box was on a worksheet.

Since it is on a form ... well ... combo boxes on forms do not have ListFillRanges. I put in this code and it seemed to work:

Dim rng As Range
    Set rng = ThisWorkbook.Sheets("ListData").Range("A2:A12")
    With Me.cmbDivision
        For Each c In rng.Cells
         .AddItem c.Text
        Next c
    End With

Fantastic, thanks for that

You're welcome. But, I must admit, I was only partly correct. I was reminded today that when the combo box is on a form, you can still tie it directly to a range in the workbook. Instead of being called the ListFillRange, it is called the RowSource. Different name, but acts exactly the same.

So, if you are still reading this thread, try this:

With Me.cmbDivision
    .RowSource = "ListData!A2:A15"
End With