Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select range fails
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select range fails
"Geoff" wrote in message
... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select range fails
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" wrote: "Geoff" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select range fails
"Geoff" wrote in message
... 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 "Rob Bovey" wrote: "Geoff" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select range fails
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" wrote: "Geoff" wrote in message ... 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 "Rob Bovey" wrote: "Geoff" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select range fails
"Geoff" wrote in message
... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select range fails
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 "Rob Bovey" wrote: "Geoff" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VBA Paste of Range with PrefixCharacter fails from VB.NET | Excel Discussion (Misc queries) | |||
select method of range class fails | Excel Programming | |||
Named Range Fails in VBA Code | Excel Programming | |||
Sheets select method fails when workbook is opened by another workbook | Excel Programming | |||
Select method of Range fails | Excel Programming |