Public Variable problem
in [Programming]
|
From: ordnance1 on 21 May 2010 02:31 I have the line below in a module: Public NoSave As Boolean In my Auto_Open routine I have: Sub Auto_Open() ' Events.Enable_Events If ActiveWorkbook.ReadOnly = True Then NoSave = True Protection.ProtectAllSheets Else NoSave = False Protection.UnProtectAllSheets End If Module7.StartPoint Module6.StartTimer End Sub Then I in ThisWorkbook ihave the following" Private Sub Workbook_BeforeClose(Cancel As Boolean) If NoSave = True Then ThisWorkbook.Close False End If If NoSave = False Then End If End Sub My problem is that when you click on either the Red X to close Excel or the smaller X to close the workbook you are still prompted to save the workbook. So it is not seeing NoSave as true, even when it was set to True in te Auto_Open routine.
From: ozgrid.com on 21 May 2010 02:46 All depends on your code line; If ActiveWorkbook.ReadOnly = True Then BTW, Public Variables MUST reside in a Public Module. -- Regards Dave Hawley www.ozgrid.com "ordnance1" <ordnance1(a)comcast.net> wrote in message news:EB6F3EAA-1883-4336-934E-B7D764168998(a)microsoft.com... >I have the line below in a module: > > Public NoSave As Boolean > > In my Auto_Open routine I have: > > Sub Auto_Open() > ' > Events.Enable_Events > > If ActiveWorkbook.ReadOnly = True Then > > NoSave = True > Protection.ProtectAllSheets > Else > NoSave = False > Protection.UnProtectAllSheets > End If > > Module7.StartPoint > Module6.StartTimer > > End Sub > > Then I in ThisWorkbook ihave the following" > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > > If NoSave = True Then > ThisWorkbook.Close False > End If > > If NoSave = False Then > End If > > End Sub > > My problem is that when you click on either the Red X to close Excel or > the smaller X to close the workbook you are still prompted to save the > workbook. So it is not seeing NoSave as true, even when it was set to True > in te Auto_Open routine.
From: Jacob Skaria on 21 May 2010 03:23 Try Private Sub Workbook_BeforeClose(Cancel As Boolean) If NoSave = True Then Cancel = True Application.DisplayAlerts = False ThisWorkbook.Close False End If End Sub -- Jacob (MVP - Excel) "ordnance1" wrote: > I have the line below in a module: > > Public NoSave As Boolean > > In my Auto_Open routine I have: > > Sub Auto_Open() > ' > Events.Enable_Events > > If ActiveWorkbook.ReadOnly = True Then > > NoSave = True > Protection.ProtectAllSheets > Else > NoSave = False > Protection.UnProtectAllSheets > End If > > Module7.StartPoint > Module6.StartTimer > > End Sub > > Then I in ThisWorkbook ihave the following" > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > > If NoSave = True Then > ThisWorkbook.Close False > End If > > If NoSave = False Then > End If > > End Sub > > My problem is that when you click on either the Red X to close Excel or the > smaller X to close the workbook you are still prompted to save the workbook. > So it is not seeing NoSave as true, even when it was set to True in te > Auto_Open routine. >
From: ordnance1 on 21 May 2010 03:38 I have other code that uses the If ActiveWorkbook.ReadOnly = True Then line and it runs fine, but when I click on the Large X (very upper right corner) it does not see the active workbook as being ReadOnly. As far as I know NoSave As Boolean is in a public module. When I commented out the line I got a Variable not defined error. I also tried simplifying the code to: If ActiveWorkbook.ReadOnly = True Then ThisWorkbook.Close False" End If "ozgrid.com" <dave(a)ozgrid.com> wrote in message news:ef9MsFL#KHA.3176(a)TK2MSFTNGP05.phx.gbl... > All depends on your code line; > > If ActiveWorkbook.ReadOnly = True Then > > BTW, Public Variables MUST reside in a Public Module. > > > > -- > Regards > Dave Hawley > www.ozgrid.com > "ordnance1" <ordnance1(a)comcast.net> wrote in message > news:EB6F3EAA-1883-4336-934E-B7D764168998(a)microsoft.com... >>I have the line below in a module: >> >> Public NoSave As Boolean >> >> In my Auto_Open routine I have: >> >> Sub Auto_Open() >> ' >> Events.Enable_Events >> >> If ActiveWorkbook.ReadOnly = True Then >> >> NoSave = True >> Protection.ProtectAllSheets >> Else >> NoSave = False >> Protection.UnProtectAllSheets >> End If >> >> Module7.StartPoint >> Module6.StartTimer >> >> End Sub >> >> Then I in ThisWorkbook ihave the following" >> >> Private Sub Workbook_BeforeClose(Cancel As Boolean) >> >> If NoSave = True Then >> ThisWorkbook.Close False >> End If >> >> If NoSave = False Then >> End If >> >> End Sub >> >> My problem is that when you click on either the Red X to close Excel or >> the smaller X to close the workbook you are still prompted to save the >> workbook. So it is not seeing NoSave as true, even when it was set to >> True in te Auto_Open routine. >
From: Dave Peterson on 21 May 2010 08:21 I'd try: Private Sub Workbook_BeforeClose(Cancel As Boolean) If NoSave = True Then me.saved = true ' a white lie to excel end if End Sub Since you lied to excel, it won't see any changes that need to be saved. So it won't prompt the user. And the close will just continue just like normal. ordnance1 wrote: > > I have the line below in a module: > > Public NoSave As Boolean > > In my Auto_Open routine I have: > > Sub Auto_Open() > ' > Events.Enable_Events > > If ActiveWorkbook.ReadOnly = True Then > > NoSave = True > Protection.ProtectAllSheets > Else > NoSave = False > Protection.UnProtectAllSheets > End If > > Module7.StartPoint > Module6.StartTimer > > End Sub > > Then I in ThisWorkbook ihave the following" > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > > If NoSave = True Then > ThisWorkbook.Close False > End If > > If NoSave = False Then > End If > > End Sub > > My problem is that when you click on either the Red X to close Excel or the > smaller X to close the workbook you are still prompted to save the workbook. > So it is not seeing NoSave as true, even when it was set to True in te > Auto_Open routine. -- Dave Peterson
|
Next
|
Last
Pages: 1 2 3 4 Prev: Save BeforeClose Next: Help changing Keyboard Shortcut assigned to Excel Macro |