Excel VBA error
 

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.

--
R Ormerod

Excel VBA error
 

I'd try:

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



R Ormerod wrote:

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.

--
R Ormerod

--

Dave Peterson

Excel VBA error
 

Worked perfectly - thanks.

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

--
R Ormerod


"Dave Peterson" wrote:

I'd try:

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



R Ormerod wrote:

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.

--
R Ormerod

--

Dave Peterson

Excel VBA error
 

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).



R Ormerod wrote:

Worked perfectly - thanks.

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

--
R Ormerod

"Dave Peterson" wrote:

I'd try:

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



R Ormerod wrote:

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.

--
R Ormerod

--

Dave Peterson


--

Dave Peterson

Excel VBA error
 

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

R Ormerod wrote:

Worked perfectly - thanks.

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

--
R Ormerod

"Dave Peterson" wrote:

I'd try:

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



R Ormerod wrote:

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.

--
R Ormerod

--

Dave Peterson


--

Dave Peterson