Combo box problem

[R Ormerod]

I have the following VBA set up for a range of combo boxes in a worksheet:

Private Sub ComboBox15_Change()

Range("C121").Select

End Sub

When trying to save the file I intermittently get the 'Select method of
Range class failed' message and the debug command takes me to the VBA for one
of my combo boxes, as above.

The only way round this I have so far found is to delete the code relating
to the affected box, but I would rather not have to do this.

 

[Dave Peterson]

Maybe...

application.goto me.range("c121")

I have the following VBA set up for a range of combo boxes in a worksheet:

Private Sub ComboBox15_Change()

Range("C121").Select

End Sub

When trying to save the file I intermittently get the 'Select method of
Range class failed' message and the debug command takes me to the VBA for one
of my combo boxes, as above.

The only way round this I have so far found is to delete the code relating
to the affected box, but I would rather not have to do this.

 

[George Nicholson]

Where is Range("C121")? What sheet?

Now, tell that to Excel.

In my experience, the most common cause of Error 1004, or any "yada method
of Range class failed", is that the Range hasn't been fully qualified. Excel
will try to use "ActiveSheet" as the default when it can, but that isn't
always feasible and it can get confused fairly easily.

....and when Excel gets confused, it generates error messages (which is why
the error is intermittent: maybe there is no activesheet at that point in
time, or the activesheet is a chart rather than a worksheet, etc.).


HTH,

 

[R Ormerod]

These ranges are all in the same worksheet as the combo boxes but the List
FillRange and LinkedCell for each box are in different worksheets.

What is the best code for selecting the active worksheet before selecting
the range to overcome this problem?