Excel VBA error

[R Ormerod]

I receive the following error message when closing an Excel file when one of
its unhidden worksheets is active, but not the other:

Run-time error '1004'
Select method of Range class failed

Debug points to code in a Private Sub (not in the Module) relating to one of
several combo boxes in the first worksheet, as follows:

Private Sub ComboBox2_Change()
Sheets("Cover").Range("C13").Select
End Sub

The LinkedCell for each combo box is a cell in the same worksheet, which in
turn feeds into a formula in the second worksheet (and I think this is where
Excel seems to be getting confused).

The ListFillRange for the combo boxes is located in a hidden worksheet.

If the Active Sheet is the forst worksheet, however, the file can be saved /
closed without any errors, so I tried this code:

Sub Workbook_BeforeClose()
Sheets("Cover").Activate
Range("A1").Select

But this did not work.

 

[Dave Peterson]

I'd try:

Application.goto Sheets("Cover").Range("C13"), scroll:=true

 

[R Ormerod]

Worked perfectly - thanks.

Would you recommend that I put this code against all the other combo boxes
in the worksheet as a safeguard?

 

[Dave Peterson]

Actually, I'd put the code in the workbook_open event.

It scares me to use the workbook_beforeclose event. I wouldn't want the user to
have to save (especially if they deleted 17 worksheets in error).

Worked perfectly - thanks.

Would you recommend that I put this code against all the other combo boxes
in the worksheet as a safeguard?

 

[Dave Peterson]

And I'd also consider dropping the linkedcell and doing everything in code.

Worked perfectly - thanks.

Would you recommend that I put this code against all the other combo boxes
in the worksheet as a safeguard?