VBA code error when using File Exit

[moogthemoog]

Hi

In Excel 2010, under a Workbook_BeforeClose event, I have code to deselect any multiple selection of sheets.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

 ActiveSheet.Select 'removes multiple selection of sheets

End Sub

This works fine, when closing an individual spreadsheet, whether or not multiple sheets are selected or not.

However, if there is more than one spreadsheet open, and Excel is exited via File/Exit, there is a "Run-time error '1004': Select method of Worksheet class failed" message.

Is there any alternative code to use that will work?

Thanks
Jon

 

[Jeffrey Mahoney]

That won't have any effect. You need to put that in the BeforeSave SUB. Usually, you would negate any Objects that you were using in the "Before Close" sub.

 

[Jaafar Tribak]

Have you tried fully quallifying the ActiveSheet ?

Private Sub Workbook_BeforeClose(Cancel As Boolean)


    [COLOR=#ff0000][B]Me.[/B][/COLOR]ActiveSheet.Select 'removes multiple selection of sheets


End Sub

 

[moogthemoog]

Thanks Jaafar & Jeffrey. Unfortunately, it still causes an error when I put in a BeforeSave event.

But thank you for your suggestions.

Cheers
Jon