Combo box problem

[Guest]

I have a number of combo boxes set up in a worksheet and Excel has
intermittently generated the 'Error 1004: Select method of Range class
failed' message when attempting to save the file after making changes to the
combo boxes.

Last week your forum advised that the following code (set up for each box
and where the debug command takes me from the error message) was the likely
source of the problem:

Private Sub ComboBox1_Change()
Range("C10").Select
End Sub

The ranges specified as above are all supposed to be in the same worksheet
as the combo boxes (N.B. the ListFillRange for the boxes are located in a
different worksheet).

The forum suggested that the range needs to be more fully qualified in the
code so that Excel doesn't get confused about the ActiveSheet.

What would you recommend as the best code for accomplishing this (as there
are so many different ways to do it)?

Thanks

 

[Guest]

It just needs to know which C10 you mean, there are potentially many across
the sheets. This is an example:
Sheets("Main").Range("C10").Select
That is if you have the sheet name or simply
Sheets(1).Range("C10").Select
if the name may change or you want to use a variable.