Reusing a list of options on a form

[collierd]

Hello

I have a list of options:

Daily, Weekly, Monthly, Never

I am looking to create 9 questions all of which use a combo box to generate the answer
The answer comes from the list above

How do I reuse this without have to do something like this for every question:

With cmbQuestion1
.AddItem "Daily"
.AddItem "Weekly"
.AddItem "Monthly"
.AddItem "Never"
End With

Thanks

[MSP77079]

Two ways:

1. put the selections in some cells somewhere (could be a hidden sheet) and use this range of cells as the "ListFillRange" for each combo box

2. do the .AddItem thing for the first combo box, then use something like

cmbQuestion2.List = cmbQuestion1.List

[collierd]

Thanks for the tips
Both work

[collierd]

Hello

How does ListFillRange work

I have:

With cmbDivision
ControlFormat.ListFillRange = Worksheets("ListData").Range("A2:A12")
End With

The key dats is in A2:a12 on the ListData worksheet
This returns an error

[MSP77079]

a couple of changes.

First, where is the code? And, what is the name of the combo box? (cannot tell from the code you posted if cmbDivision is a variable or the name of the box)

If the code is on the window of the sheet that contains the combo box, then the code would be:

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

Note that the ListFillRange is a String, not a Range.

If the code is in a module, and you use the Object name for the Worksheet containing the combo box, and the Object name of that worksheet (NOT the name on the tab) is Sheet1 then the code is:

With Sheet1.cmbDivision
    .ListFillRange = "ListData!A2:A15"
End With

If you are using the name on the tab of the worksheet, then it would have to be something like this:

With Sheets("mySheet").OLEObjects("cmbDivision")
    .ListFillRange = "ListData!A2:A15"
End With

[collierd]

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