Combo box VBA error

[R Ormerod]

I have 16 x combo boxes set up in a cover worksheet with List Fill Ranges
determining the values in the drop-down list and Linked Cells where the
results of the selection are sent in 2 x other worksheets respectively.

When I attempt to Close or Save the workbook with the active worksheet being
either of those other than the cover, the Run Time Error 1004: Select method
of Range class failed occurs.

Debug always selects the Private Sub ComboBox_Change() code for one
particular combo box:

Sheets("Cover").Range("C13").Select

This is identical to the code for all the other boxes (apart from the cell
reference), the idea being that as soon as a selection has been made the
combo box contents are deselected and the cursor moves to the cell below the
box.

The following code was recommended by this forum which resolved the problem:

Application.Goto Sheets("Cover").Range("C13"), Scroll:=False

However, this new code means that the contents of this combo box after
drop-down selection are still highlighted and can be edited. I changed the
combo box Style to 2-fmStyleDropDownList, but this still means that any other
value in the list can be selected (e.g. by using the arrow keys).

Another problem has now occurred when I attempt to Save As. The run time
error as above occurs, but debug now highlights the Private Sub code for all
of the other combo boxes in turn which still have the original code.

I can change the code for all the other boxes to overcome this, but this
raises two questions:

1) How can I add to the new code to deselect the combo box contents?

2) How could Save As cause the run time error in all the other boxes but
Close or Save only in one?

 

[Dave Peterson]

IIRC, this happens when the linkedcell or listfillrange is on the same sheet.
(I'm not sure which one--or if it's both).

Can you move those linkedcells and listfillranges to another sheet?

Or even drop the linkedcells and listfillranges and do everything in code?

 

[R Ormerod]

Actually, I was incorrect below - the workbook contains 3 x worksheets, as
follows:

Cover - combo boxes and linked cells
Report - formulae referencing the linked cells on the Cover worksheet
Data - list fill ranges for all the combo boxes in the Cover worksheet

How does this affect things?

Doing everything in code is an option but I don't really have the time!

 

[Dave Peterson]

What happens when you move the linked cells to a different sheet?

Actually, I was incorrect below - the workbook contains 3 x worksheets, as
follows:

Cover - combo boxes and linked cells
Report - formulae referencing the linked cells on the Cover worksheet
Data - list fill ranges for all the combo boxes in the Cover worksheet

How does this affect things?

Doing everything in code is an option but I don't really have the time!

 

[R Ormerod]

I'll set up all the linked cells in the Data worksheet and test it.

Unfortunately I haven't got time to complete this today and know that these
threads go 'dead' after a few days.

If I reply to this message next week will you still pick it up?

 

[Dave Peterson]

Someone will pick it the thread.

I'll set up all the linked cells in the Data worksheet and test it.

Unfortunately I haven't got time to complete this today and know that these
threads go 'dead' after a few days.

If I reply to this message next week will you still pick it up?