Select range fails

[Geoff]

The code is in ThisWorkbook. There are problems when quitting Excel with
several wbooks open. If this is the only workbook open the code runs ok but
fails if there are others open as well. I thought by placing it in
ThisWorkbook module it was specific but maybe not. How should I qualify the
Select statement please?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Range("A100").Select 'Fails here if other wbooks
are open

ActiveWindow.DisplayGridlines = False

Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True

End Sub

T.I.A.

Geoff

 

[Rob Bovey]

The code is in ThisWorkbook. There are problems when quitting Excel with
several wbooks open. If this is the only workbook open the code runs ok
but
fails if there are others open as well. I thought by placing it in
ThisWorkbook module it was specific but maybe not. How should I qualify
the
Select statement please?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Range("A100").Select 'Fails here if other
wbooks
are open

ActiveWindow.DisplayGridlines = False

Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True

End Sub

Hi Geoff,

You have to activate a worksheet before you can select a cell on it. I
suspect this is the problem. Change your select code to the following:

With Sheets(Sheets.Count)
.Activate
.Range("A100").Select
End With

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Geoff]

Hi Rob
I find the expected quitting of Excel with 1 click depends on which wbook is
activated. If the activeworkbook is not the 'target' wbook then whilst the
wbooks are closed, the application remains open and requires a 2nd click to
quit it. And of course with the 2nd click the 'target' wbook seems toi be
saved again.

Geoff

 

[Rob Bovey]

Hi Rob
I find the expected quitting of Excel with 1 click depends on which wbook
is
activated. If the activeworkbook is not the 'target' wbook then whilst
the
wbooks are closed, the application remains open and requires a 2nd click
to
quit it. And of course with the 2nd click the 'target' wbook seems toi be
saved again.

Hi Geoff,

This indeed appears to be a bug in the Excel object model. I can't
remember ever coming across it myself, but I did find one other reference to
it in the Google newsgroup archives. I think the easiest solution is to get
rid of the Workbook_BeforeClose event and put this code in an Auto_Close
procedure in the regular code module. It seems to work fine for me when run
that way.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Geoff]

Hi Rob
I have seen a number of threads referring to Auto_Close saying exactly as
you have - it works.

I have not used Auto_Close before and wonder if it is put in a standard
module then how is it called? Or is it called simply by clicking the Quit
button?

Thank you so far.

Geoff

 

[Rob Bovey]

I have seen a number of threads referring to Auto_Close saying exactly as
you have - it works.

I have not used Auto_Close before and wonder if it is put in a standard
module then how is it called? Or is it called simply by clicking the Quit
button?

Hi Geoff,

Auto_Close is a special name for a procedure that Excel VBA recognizes
and runs when a workbook is closed. Prior to Excel 97, when there was no
support for events, the Auto_Close procedure was the only way to
automatically run code when a workbook closed. It is still fully supported
and it still has some advantages over the Workbook_BeforeClose event, this
being one of them.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Geoff]

Hi Rob
That's interesting. Ok, this is something I need to research before I can
apply.

Many thanks for the guidance and help.

Geoff