I have a user form that has three diffferent drop boxes, as well as multiple
check boxes on it. I have altered the way that I load the combo boxes
numerous times to make sure that they load correctly, such as:

Combobox1.rowsource = ShtLists.range("List1).values
Combobox1.RowSource =
ThisWorkbook.Sheets("TheLists").Range("g3:g4").Add ress(external:=True)

The problem is that even when they appear to load and operate correctly if,
(once I am back on the Excel page) if I close the file without saving Excel
aborts. This only happens if I have opened the user form and selected one of
the combo boxes.

I have named ranges that contain these lists and have named the sheets as
well, such that the list I want to load in ComboBox1 is really on sheet
"ShtLists" in range "List1", so usually I would reference as
shtlists.range("List1"). I have reverted back to Sheet Names and cell
references to try and eliminate any of the object problems I have read about.

I really need to know the cleanest way to load these drop boxes and if I
need to clean them out before closing the form.

Thank you in advance for any help.

It is not what is in the combobox that causes the error. It is most likely
the code to load the data in the combobox or the event code that executes
when the control is clicked.

For instance:

Combobox1.rowsource = ShtLists.range("List1).values

This code will not work because the row source has to be in A1 notation, not
values. The result of any code that you use has to equate to one of these
two formats:

Sheet1!a1:d10 or a1:d10

The Sheet reference must be used when the row source is not on the active
sheet.



"Troubled User" wrote:

I have a user form that has three diffferent drop boxes, as well as multiple
check boxes on it. I have altered the way that I load the combo boxes
numerous times to make sure that they load correctly, such as:

Combobox1.rowsource = ShtLists.range("List1).values
Combobox1.RowSource =
ThisWorkbook.Sheets("TheLists").Range("g3:g4").Add ress(external:=True)

The problem is that even when they appear to load and operate correctly if,
(once I am back on the Excel page) if I close the file without saving Excel
aborts. This only happens if I have opened the user form and selected one of
the combo boxes.

I have named ranges that contain these lists and have named the sheets as
well, such that the list I want to load in ComboBox1 is really on sheet
"ShtLists" in range "List1", so usually I would reference as
shtlists.range("List1"). I have reverted back to Sheet Names and cell
references to try and eliminate any of the object problems I have read about.

I really need to know the cleanest way to load these drop boxes and if I
need to clean them out before closing the form.

Thank you in advance for any help.

Do I have to reference the sheet as Sheet1! or can I use the named sheet
names such as sht1?

What about checkboxes, can they reference named ranges?

On the save of the file it also turn the left side of the screen blue, if
that is any help.

Thanks,


"JLGWhiz" wrote:

It is not what is in the combobox that causes the error. It is most likely
the code to load the data in the combobox or the event code that executes
when the control is clicked.

For instance:

Combobox1.rowsource = ShtLists.range("List1).values

This code will not work because the row source has to be in A1 notation, not
values. The result of any code that you use has to equate to one of these
two formats:

Sheet1!a1:d10 or a1:d10

The Sheet reference must be used when the row source is not on the active
sheet.



"Troubled User" wrote:

I have a user form that has three diffferent drop boxes, as well as multiple
check boxes on it. I have altered the way that I load the combo boxes
numerous times to make sure that they load correctly, such as:

Combobox1.rowsource = ShtLists.range("List1).values
Combobox1.RowSource =
ThisWorkbook.Sheets("TheLists").Range("g3:g4").Add ress(external:=True)

The problem is that even when they appear to load and operate correctly if,
(once I am back on the Excel page) if I close the file without saving Excel
aborts. This only happens if I have opened the user form and selected one of
the combo boxes.

I have named ranges that contain these lists and have named the sheets as
well, such that the list I want to load in ComboBox1 is really on sheet
"ShtLists" in range "List1", so usually I would reference as
shtlists.range("List1"). I have reverted back to Sheet Names and cell
references to try and eliminate any of the object problems I have read about.

I really need to know the cleanest way to load these drop boxes and if I
need to clean them out before closing the form.

Thank you in advance for any help.