Public Variable problem
in [Programming]
|
From: ordnance1 on 22 May 2010 19:02 Again I think it is important to not that if I am not running the Timer Routine everything works as it should. "ordnance1" <ordnance1(a)comcast.net> wrote in message news:elmozGg#KHA.348(a)TK2MSFTNGP06.phx.gbl... > Here is my Workbook_BeforeClose incorporating you suggestion, and it run > the NoSave False IF statement regardless if the workbook is opened read > only or not > > > If NoSave = True Then 'Workbook opened as read only > Me.Saved = True ' a white lie to excel > msgbox "NoSave = True" > End If > > If NoSave = False Then 'Workbook not opened as read only > msgbox "NoSave = False" > End If > > "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message > news:4BF8244B.29C1E35E(a)verizonXSPAM.net... >> What happened when you tested my suggestion? >> >> ordnance1 wrote: >>> >>> I'm sorry, I did try all suggestions offered. The only one that worked >>> was >>> the one where I lied to Excel and said If ActiveWorkbook.ReadOnly = >>> False >>> then perform the function that I wanted done when the document opened as >>> read only. The problem with that approach is that when the document is >>> opened, not as read only, it would close without saving. I have other >>> modules where I use the If ActiveWorkbook.ReadOnly = and the all work >>> correctly. Every thing comes back to the timer routine. If I do not run >>> it >>> (it is started in the Auto_Open) everything works as expected. I even >>> tried >>> referring to the sub (shown below). That sub resided in the same module >>> as >>> the timer routine. >>> >>> ------------------------- >>> Sub StopTimer() >>> Cancel = True >>> End Sub >>> ------------------------ >>> >>> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message >>> news:4BF7BB1B.6522AE15(a)verizonXSPAM.net... >>> > Did you try all the suggestions? >>> > >>> > >>> > ordnance1 wrote: >>> >> >>> >> Sorry I did not get back sooner, got called away for work. >>> >> >>> >> Things still are not working for me and I think I have tracked down >>> >> my >>> >> problem, just do not know how to fix it. The code below is a timer >>> >> code >>> >> to >>> >> close my workbook after 15 minutes and it prevents my >>> >> Workbook_BeforeClose >>> >> from working. >>> >> When I click on the small x or select close from the menu it works, >>> >> but >>> >> when >>> >> I click on the large X it sees ActiveWorkbook.ReadOnly as false. >>> >> >>> >> Private Sub Workbook_BeforeClose(Cancel As Boolean) >>> >> >>> >> If ActiveWorkbook.ReadOnly = False Then >>> >> ThisWorkbook.Close True >>> >> End If >>> >> >>> >> If ActiveWorkbook.ReadOnly = True Then >>> >> ThisWorkbook.Close False >>> >> End If >>> >> >>> >> End Sub >>> >> >>> >> ''''''''''''''''''''''''''''''''''''''''''''''''''' >>> >> >>> >> Code was found @ >>> >> http://www.teachexcel.com/free-excel-macros/m-13,close-workbook-time-limit.html >>> >> >>> >> Option Explicit >>> >> Sub StartTimer() >>> >> Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes >>> >> Application.DisplayAlerts = True >>> >> Cancel = False >>> >> >>> >> TimeInMinutes = 15 'Timer is set for 15 minutes; change as needed. >>> >> If TimeInMinutes > 5 Then >>> >> TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60) >>> >> Start = Timer >>> >> Do While Timer < Start + TotalTimeInMinutes >>> >> DoEvents >>> >> >>> >> If Cancel = True Then >>> >> GoTo Finished >>> >> End If >>> >> >>> >> Loop >>> >> Finish = Timer >>> >> TotalTime = Finish - Start >>> >> Application.DisplayAlerts = False >>> >> >>> >> End If >>> >> >>> >> Start = Timer >>> >> Do While Timer < Start + (5 * 60) >>> >> DoEvents >>> >> >>> >> If Cancel = True Then >>> >> GoTo Finished >>> >> End If >>> >> >>> >> Loop >>> >> Finish = Timer >>> >> TotalTime = Finish - Start >>> >> Application.DisplayAlerts = False >>> >> >>> >> Finished: >>> >> >>> >> If ActiveWorkbook.ReadOnly = True Then >>> >> ThisWorkbook.Close False >>> >> Else >>> >> ThisWorkbook.Close True >>> >> End If >>> >> >>> >> End Sub >>> >> >>> >> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' >>> > >>> > -- >>> > >>> > Dave Peterson >> >> -- >> >> Dave Peterson >
From: Dave Peterson on 22 May 2010 20:08 That means something is changing the value of that NoSave variable. Or you don't have that variable declared as a Public variable in a General module. If all you're checking is the .readonly property, you could use: Private Sub Workbook_BeforeClose(Cancel As Boolean) if me.readonly = true then me.saved = true end if End Sub I have no idea how that variable is used/set/changed by other routines, though. ordnance1 wrote: > > Here is my Workbook_BeforeClose incorporating you suggestion, and it run > the NoSave False IF statement regardless if the workbook is opened read only > or not > > If NoSave = True Then 'Workbook opened as read only > Me.Saved = True ' a white lie to excel > msgbox "NoSave = True" > End If > > If NoSave = False Then 'Workbook not opened as read only > msgbox "NoSave = False" > End If > > "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message > news:4BF8244B.29C1E35E(a)verizonXSPAM.net... > > What happened when you tested my suggestion? > > > > ordnance1 wrote: > >> > >> I'm sorry, I did try all suggestions offered. The only one that worked > >> was > >> the one where I lied to Excel and said If ActiveWorkbook.ReadOnly = False > >> then perform the function that I wanted done when the document opened as > >> read only. The problem with that approach is that when the document is > >> opened, not as read only, it would close without saving. I have other > >> modules where I use the If ActiveWorkbook.ReadOnly = and the all work > >> correctly. Every thing comes back to the timer routine. If I do not run > >> it > >> (it is started in the Auto_Open) everything works as expected. I even > >> tried > >> referring to the sub (shown below). That sub resided in the same module > >> as > >> the timer routine. > >> > >> ------------------------- > >> Sub StopTimer() > >> Cancel = True > >> End Sub > >> ------------------------ > >> > >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message > >> news:4BF7BB1B.6522AE15(a)verizonXSPAM.net... > >> > Did you try all the suggestions? > >> > > >> > > >> > ordnance1 wrote: > >> >> > >> >> Sorry I did not get back sooner, got called away for work. > >> >> > >> >> Things still are not working for me and I think I have tracked down my > >> >> problem, just do not know how to fix it. The code below is a timer > >> >> code > >> >> to > >> >> close my workbook after 15 minutes and it prevents my > >> >> Workbook_BeforeClose > >> >> from working. > >> >> When I click on the small x or select close from the menu it works, > >> >> but > >> >> when > >> >> I click on the large X it sees ActiveWorkbook.ReadOnly as false. > >> >> > >> >> Private Sub Workbook_BeforeClose(Cancel As Boolean) > >> >> > >> >> If ActiveWorkbook.ReadOnly = False Then > >> >> ThisWorkbook.Close True > >> >> End If > >> >> > >> >> If ActiveWorkbook.ReadOnly = True Then > >> >> ThisWorkbook.Close False > >> >> End If > >> >> > >> >> End Sub > >> >> > >> >> ''''''''''''''''''''''''''''''''''''''''''''''''''' > >> >> > >> >> Code was found @ > >> >> http://www.teachexcel.com/free-excel-macros/m-13,close-workbook-time-limit.html > >> >> > >> >> Option Explicit > >> >> Sub StartTimer() > >> >> Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes > >> >> Application.DisplayAlerts = True > >> >> Cancel = False > >> >> > >> >> TimeInMinutes = 15 'Timer is set for 15 minutes; change as needed. > >> >> If TimeInMinutes > 5 Then > >> >> TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60) > >> >> Start = Timer > >> >> Do While Timer < Start + TotalTimeInMinutes > >> >> DoEvents > >> >> > >> >> If Cancel = True Then > >> >> GoTo Finished > >> >> End If > >> >> > >> >> Loop > >> >> Finish = Timer > >> >> TotalTime = Finish - Start > >> >> Application.DisplayAlerts = False > >> >> > >> >> End If > >> >> > >> >> Start = Timer > >> >> Do While Timer < Start + (5 * 60) > >> >> DoEvents > >> >> > >> >> If Cancel = True Then > >> >> GoTo Finished > >> >> End If > >> >> > >> >> Loop > >> >> Finish = Timer > >> >> TotalTime = Finish - Start > >> >> Application.DisplayAlerts = False > >> >> > >> >> Finished: > >> >> > >> >> If ActiveWorkbook.ReadOnly = True Then > >> >> ThisWorkbook.Close False > >> >> Else > >> >> ThisWorkbook.Close True > >> >> End If > >> >> > >> >> End Sub > >> >> > >> >> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' > >> > > >> > -- > >> > > >> > Dave Peterson > > > > -- > > > > Dave Peterson -- Dave Peterson
From: ordnance1 on 22 May 2010 20:43 Well I appreciate your help, but it still does not wok. Something that might be of interest is, If I click on the large X the code does not work (even though the workbook is read only) but if I click on cancel when asked if I want to save changes, and then click on the large X again, the code works. "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message news:4BF871E1.79C4AD21(a)verizonXSPAM.net... > That means something is changing the value of that NoSave variable. > > Or you don't have that variable declared as a Public variable in a General > module. > > If all you're checking is the .readonly property, you could use: > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > if me.readonly = true then > me.saved = true > end if > End Sub > > I have no idea how that variable is used/set/changed by other routines, > though. > > ordnance1 wrote: >> >> Here is my Workbook_BeforeClose incorporating you suggestion, and it run >> the NoSave False IF statement regardless if the workbook is opened read >> only >> or not >> >> If NoSave = True Then 'Workbook opened as read only >> Me.Saved = True ' a white lie to excel >> msgbox "NoSave = True" >> End If >> >> If NoSave = False Then 'Workbook not opened as read only >> msgbox "NoSave = False" >> End If >> >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message >> news:4BF8244B.29C1E35E(a)verizonXSPAM.net... >> > What happened when you tested my suggestion? >> > >> > ordnance1 wrote: >> >> >> >> I'm sorry, I did try all suggestions offered. The only one that worked >> >> was >> >> the one where I lied to Excel and said If ActiveWorkbook.ReadOnly = >> >> False >> >> then perform the function that I wanted done when the document opened >> >> as >> >> read only. The problem with that approach is that when the document is >> >> opened, not as read only, it would close without saving. I have other >> >> modules where I use the If ActiveWorkbook.ReadOnly = and the all work >> >> correctly. Every thing comes back to the timer routine. If I do not >> >> run >> >> it >> >> (it is started in the Auto_Open) everything works as expected. I even >> >> tried >> >> referring to the sub (shown below). That sub resided in the same >> >> module >> >> as >> >> the timer routine. >> >> >> >> ------------------------- >> >> Sub StopTimer() >> >> Cancel = True >> >> End Sub >> >> ------------------------ >> >> >> >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message >> >> news:4BF7BB1B.6522AE15(a)verizonXSPAM.net... >> >> > Did you try all the suggestions? >> >> > >> >> > >> >> > ordnance1 wrote: >> >> >> >> >> >> Sorry I did not get back sooner, got called away for work. >> >> >> >> >> >> Things still are not working for me and I think I have tracked down >> >> >> my >> >> >> problem, just do not know how to fix it. The code below is a timer >> >> >> code >> >> >> to >> >> >> close my workbook after 15 minutes and it prevents my >> >> >> Workbook_BeforeClose >> >> >> from working. >> >> >> When I click on the small x or select close from the menu it works, >> >> >> but >> >> >> when >> >> >> I click on the large X it sees ActiveWorkbook.ReadOnly as false. >> >> >> >> >> >> Private Sub Workbook_BeforeClose(Cancel As Boolean) >> >> >> >> >> >> If ActiveWorkbook.ReadOnly = False Then >> >> >> ThisWorkbook.Close True >> >> >> End If >> >> >> >> >> >> If ActiveWorkbook.ReadOnly = True Then >> >> >> ThisWorkbook.Close False >> >> >> End If >> >> >> >> >> >> End Sub >> >> >> >> >> >> ''''''''''''''''''''''''''''''''''''''''''''''''''' >> >> >> >> >> >> Code was found @ >> >> >> http://www.teachexcel.com/free-excel-macros/m-13,close-workbook-time-limit.html >> >> >> >> >> >> Option Explicit >> >> >> Sub StartTimer() >> >> >> Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes >> >> >> Application.DisplayAlerts = True >> >> >> Cancel = False >> >> >> >> >> >> TimeInMinutes = 15 'Timer is set for 15 minutes; change as needed. >> >> >> If TimeInMinutes > 5 Then >> >> >> TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60) >> >> >> Start = Timer >> >> >> Do While Timer < Start + TotalTimeInMinutes >> >> >> DoEvents >> >> >> >> >> >> If Cancel = True Then >> >> >> GoTo Finished >> >> >> End If >> >> >> >> >> >> Loop >> >> >> Finish = Timer >> >> >> TotalTime = Finish - Start >> >> >> Application.DisplayAlerts = False >> >> >> >> >> >> End If >> >> >> >> >> >> Start = Timer >> >> >> Do While Timer < Start + (5 * 60) >> >> >> DoEvents >> >> >> >> >> >> If Cancel = True Then >> >> >> GoTo Finished >> >> >> End If >> >> >> >> >> >> Loop >> >> >> Finish = Timer >> >> >> TotalTime = Finish - Start >> >> >> Application.DisplayAlerts = False >> >> >> >> >> >> Finished: >> >> >> >> >> >> If ActiveWorkbook.ReadOnly = True Then >> >> >> ThisWorkbook.Close False >> >> >> Else >> >> >> ThisWorkbook.Close True >> >> >> End If >> >> >> >> >> >> End Sub >> >> >> >> >> >> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' >> >> > >> >> > -- >> >> > >> >> > Dave Peterson >> > >> > -- >> > >> > Dave Peterson > > -- > > Dave Peterson
From: Dave Peterson on 23 May 2010 06:43 Start a new workbook. Add that single procedure to the ThisWorkbook module. Save the workbook (book1.xls is ok with me). Close that workbook. Reopen it in readonly mode. Make some changes. Close the workbook. Do you get prompted to save the changes? Reopen the workbook in normal (not readonly) mode. Make some changes Close the workbook. Do you get prompted to save the changes? Try the same tests when you close excel and when you close the single workbook. ordnance1 wrote: > > Well I appreciate your help, but it still does not wok. Something that might > be of interest is, If I click on the large X the code does not work (even > though the workbook is read only) but if I click on cancel when asked if I > want to save changes, and then click on the large X again, the code works. > > "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message > news:4BF871E1.79C4AD21(a)verizonXSPAM.net... > > That means something is changing the value of that NoSave variable. > > > > Or you don't have that variable declared as a Public variable in a General > > module. > > > > If all you're checking is the .readonly property, you could use: > > > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > > if me.readonly = true then > > me.saved = true > > end if > > End Sub > > > > I have no idea how that variable is used/set/changed by other routines, > > though. > > > > ordnance1 wrote: > >> > >> Here is my Workbook_BeforeClose incorporating you suggestion, and it run > >> the NoSave False IF statement regardless if the workbook is opened read > >> only > >> or not > >> > >> If NoSave = True Then 'Workbook opened as read only > >> Me.Saved = True ' a white lie to excel > >> msgbox "NoSave = True" > >> End If > >> > >> If NoSave = False Then 'Workbook not opened as read only > >> msgbox "NoSave = False" > >> End If > >> > >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message > >> news:4BF8244B.29C1E35E(a)verizonXSPAM.net... > >> > What happened when you tested my suggestion? > >> > > >> > ordnance1 wrote: > >> >> > >> >> I'm sorry, I did try all suggestions offered. The only one that worked > >> >> was > >> >> the one where I lied to Excel and said If ActiveWorkbook.ReadOnly = > >> >> False > >> >> then perform the function that I wanted done when the document opened > >> >> as > >> >> read only. The problem with that approach is that when the document is > >> >> opened, not as read only, it would close without saving. I have other > >> >> modules where I use the If ActiveWorkbook.ReadOnly = and the all work > >> >> correctly. Every thing comes back to the timer routine. If I do not > >> >> run > >> >> it > >> >> (it is started in the Auto_Open) everything works as expected. I even > >> >> tried > >> >> referring to the sub (shown below). That sub resided in the same > >> >> module > >> >> as > >> >> the timer routine. > >> >> > >> >> ------------------------- > >> >> Sub StopTimer() > >> >> Cancel = True > >> >> End Sub > >> >> ------------------------ > >> >> > >> >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message > >> >> news:4BF7BB1B.6522AE15(a)verizonXSPAM.net... > >> >> > Did you try all the suggestions? > >> >> > > >> >> > > >> >> > ordnance1 wrote: > >> >> >> > >> >> >> Sorry I did not get back sooner, got called away for work. > >> >> >> > >> >> >> Things still are not working for me and I think I have tracked down > >> >> >> my > >> >> >> problem, just do not know how to fix it. The code below is a timer > >> >> >> code > >> >> >> to > >> >> >> close my workbook after 15 minutes and it prevents my > >> >> >> Workbook_BeforeClose > >> >> >> from working. > >> >> >> When I click on the small x or select close from the menu it works, > >> >> >> but > >> >> >> when > >> >> >> I click on the large X it sees ActiveWorkbook.ReadOnly as false. > >> >> >> > >> >> >> Private Sub Workbook_BeforeClose(Cancel As Boolean) > >> >> >> > >> >> >> If ActiveWorkbook.ReadOnly = False Then > >> >> >> ThisWorkbook.Close True > >> >> >> End If > >> >> >> > >> >> >> If ActiveWorkbook.ReadOnly = True Then > >> >> >> ThisWorkbook.Close False > >> >> >> End If > >> >> >> > >> >> >> End Sub > >> >> >> > >> >> >> ''''''''''''''''''''''''''''''''''''''''''''''''''' > >> >> >> > >> >> >> Code was found @ > >> >> >> http://www.teachexcel.com/free-excel-macros/m-13,close-workbook-time-limit.html > >> >> >> > >> >> >> Option Explicit > >> >> >> Sub StartTimer() > >> >> >> Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes > >> >> >> Application.DisplayAlerts = True > >> >> >> Cancel = False > >> >> >> > >> >> >> TimeInMinutes = 15 'Timer is set for 15 minutes; change as needed. > >> >> >> If TimeInMinutes > 5 Then > >> >> >> TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60) > >> >> >> Start = Timer > >> >> >> Do While Timer < Start + TotalTimeInMinutes > >> >> >> DoEvents > >> >> >> > >> >> >> If Cancel = True Then > >> >> >> GoTo Finished > >> >> >> End If > >> >> >> > >> >> >> Loop > >> >> >> Finish = Timer > >> >> >> TotalTime = Finish - Start > >> >> >> Application.DisplayAlerts = False > >> >> >> > >> >> >> End If > >> >> >> > >> >> >> Start = Timer > >> >> >> Do While Timer < Start + (5 * 60) > >> >> >> DoEvents > >> >> >> > >> >> >> If Cancel = True Then > >> >> >> GoTo Finished > >> >> >> End If > >> >> >> > >> >> >> Loop > >> >> >> Finish = Timer > >> >> >> TotalTime = Finish - Start > >> >> >> Application.DisplayAlerts = False > >> >> >> > >> >> >> Finished: > >> >> >> > >> >> >> If ActiveWorkbook.ReadOnly = True Then > >> >> >> ThisWorkbook.Close False > >> >> >> Else > >> >> >> ThisWorkbook.Close True > >> >> >> End If > >> >> >> > >> >> >> End Sub > >> >> >> > >> >> >> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' > >> >> > > >> >> > -- > >> >> > > >> >> > Dave Peterson > >> > > >> > -- > >> > > >> > Dave Peterson > > > > -- > > > > Dave Peterson -- Dave Peterson
From: ordnance1 on 23 May 2010 22:01 It worked just as expected. And I can make my current workbook act correctly by not running my Timer code (see below) something in that code prevents the before close routine from seeing the workbook as read only. Option Explicit Sub StartTimer() Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes Application.DisplayAlerts = True TimerStop = False 'was cancel TimeInMinutes = 15 'Timer is set for 15 minutes; change as needed. If TimeInMinutes > 5 Then TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60) Start = Timer Do While Timer < Start + TotalTimeInMinutes DoEvents If TimerStop = True Then 'was cancel 'Exit Do GoTo finished End If Loop Finish = Timer TotalTime = Finish - Start Application.DisplayAlerts = False End If Start = Timer Do While Timer < Start + (5 * 60) DoEvents If TimerStop = True Then 'was cancel 'Exit Do GoTo finished End If Loop Finish = Timer TotalTime = Finish - Start Application.DisplayAlerts = False If ActiveWorkbook.ReadOnly = True Then ThisWorkbook.Close False Else ThisWorkbook.Close True End If End Sub "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message news:4BF906C5.FCBF3B0B(a)verizonXSPAM.net... > Start a new workbook. > Add that single procedure to the ThisWorkbook module. > Save the workbook (book1.xls is ok with me). > > Close that workbook. > Reopen it in readonly mode. > Make some changes. > Close the workbook. > > Do you get prompted to save the changes? > > > > Reopen the workbook in normal (not readonly) mode. > Make some changes > Close the workbook. > > Do you get prompted to save the changes? > > Try the same tests when you close excel and when you close the single > workbook. > > > > ordnance1 wrote: >> >> Well I appreciate your help, but it still does not wok. Something that >> might >> be of interest is, If I click on the large X the code does not work (even >> though the workbook is read only) but if I click on cancel when asked if >> I >> want to save changes, and then click on the large X again, the code >> works. >> >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message >> news:4BF871E1.79C4AD21(a)verizonXSPAM.net... >> > That means something is changing the value of that NoSave variable. >> > >> > Or you don't have that variable declared as a Public variable in a >> > General >> > module. >> > >> > If all you're checking is the .readonly property, you could use: >> > >> > Private Sub Workbook_BeforeClose(Cancel As Boolean) >> > if me.readonly = true then >> > me.saved = true >> > end if >> > End Sub >> > >> > I have no idea how that variable is used/set/changed by other routines, >> > though. >> > >> > ordnance1 wrote: >> >> >> >> Here is my Workbook_BeforeClose incorporating you suggestion, and it >> >> run >> >> the NoSave False IF statement regardless if the workbook is opened >> >> read >> >> only >> >> or not >> >> >> >> If NoSave = True Then 'Workbook opened as read only >> >> Me.Saved = True ' a white lie to excel >> >> msgbox "NoSave = True" >> >> End If >> >> >> >> If NoSave = False Then 'Workbook not opened as read only >> >> msgbox "NoSave = False" >> >> End If >> >> >> >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message >> >> news:4BF8244B.29C1E35E(a)verizonXSPAM.net... >> >> > What happened when you tested my suggestion? >> >> > >> >> > ordnance1 wrote: >> >> >> >> >> >> I'm sorry, I did try all suggestions offered. The only one that >> >> >> worked >> >> >> was >> >> >> the one where I lied to Excel and said If ActiveWorkbook.ReadOnly = >> >> >> False >> >> >> then perform the function that I wanted done when the document >> >> >> opened >> >> >> as >> >> >> read only. The problem with that approach is that when the document >> >> >> is >> >> >> opened, not as read only, it would close without saving. I have >> >> >> other >> >> >> modules where I use the If ActiveWorkbook.ReadOnly = and the all >> >> >> work >> >> >> correctly. Every thing comes back to the timer routine. If I do not >> >> >> run >> >> >> it >> >> >> (it is started in the Auto_Open) everything works as expected. I >> >> >> even >> >> >> tried >> >> >> referring to the sub (shown below). That sub resided in the same >> >> >> module >> >> >> as >> >> >> the timer routine. >> >> >> >> >> >> ------------------------- >> >> >> Sub StopTimer() >> >> >> Cancel = True >> >> >> End Sub >> >> >> ------------------------ >> >> >> >> >> >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message >> >> >> news:4BF7BB1B.6522AE15(a)verizonXSPAM.net... >> >> >> > Did you try all the suggestions? >> >> >> > >> >> >> > >> >> >> > ordnance1 wrote: >> >> >> >> >> >> >> >> Sorry I did not get back sooner, got called away for work. >> >> >> >> >> >> >> >> Things still are not working for me and I think I have tracked >> >> >> >> down >> >> >> >> my >> >> >> >> problem, just do not know how to fix it. The code below is a >> >> >> >> timer >> >> >> >> code >> >> >> >> to >> >> >> >> close my workbook after 15 minutes and it prevents my >> >> >> >> Workbook_BeforeClose >> >> >> >> from working. >> >> >> >> When I click on the small x or select close from the menu it >> >> >> >> works, >> >> >> >> but >> >> >> >> when >> >> >> >> I click on the large X it sees ActiveWorkbook.ReadOnly as >> >> >> >> false. >> >> >> >> >> >> >> >> Private Sub Workbook_BeforeClose(Cancel As Boolean) >> >> >> >> >> >> >> >> If ActiveWorkbook.ReadOnly = False Then >> >> >> >> ThisWorkbook.Close True >> >> >> >> End If >> >> >> >> >> >> >> >> If ActiveWorkbook.ReadOnly = True Then >> >> >> >> ThisWorkbook.Close False >> >> >> >> End If >> >> >> >> >> >> >> >> End Sub >> >> >> >> >> >> >> >> ''''''''''''''''''''''''''''''''''''''''''''''''''' >> >> >> >> >> >> >> >> Code was found @ >> >> >> >> http://www.teachexcel.com/free-excel-macros/m-13,close-workbook-time-limit.html >> >> >> >> >> >> >> >> Option Explicit >> >> >> >> Sub StartTimer() >> >> >> >> Dim Start, Finish, TotalTime, TotalTimeInMinutes, TimeInMinutes >> >> >> >> Application.DisplayAlerts = True >> >> >> >> Cancel = False >> >> >> >> >> >> >> >> TimeInMinutes = 15 'Timer is set for 15 minutes; change as >> >> >> >> needed. >> >> >> >> If TimeInMinutes > 5 Then >> >> >> >> TotalTimeInMinutes = (TimeInMinutes * 60) - (5 * 60) >> >> >> >> Start = Timer >> >> >> >> Do While Timer < Start + TotalTimeInMinutes >> >> >> >> DoEvents >> >> >> >> >> >> >> >> If Cancel = True Then >> >> >> >> GoTo Finished >> >> >> >> End If >> >> >> >> >> >> >> >> Loop >> >> >> >> Finish = Timer >> >> >> >> TotalTime = Finish - Start >> >> >> >> Application.DisplayAlerts = False >> >> >> >> >> >> >> >> End If >> >> >> >> >> >> >> >> Start = Timer >> >> >> >> Do While Timer < Start + (5 * 60) >> >> >> >> DoEvents >> >> >> >> >> >> >> >> If Cancel = True Then >> >> >> >> GoTo Finished >> >> >> >> End If >> >> >> >> >> >> >> >> Loop >> >> >> >> Finish = Timer >> >> >> >> TotalTime = Finish - Start >> >> >> >> Application.DisplayAlerts = False >> >> >> >> >> >> >> >> Finished: >> >> >> >> >> >> >> >> If ActiveWorkbook.ReadOnly = True Then >> >> >> >> ThisWorkbook.Close False >> >> >> >> Else >> >> >> >> ThisWorkbook.Close True >> >> >> >> End If >> >> >> >> >> >> >> >> End Sub >> >> >> >> >> >> >> >> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' >> >> >> > >> >> >> > -- >> >> >> > >> >> >> > Dave Peterson >> >> > >> >> > -- >> >> > >> >> > Dave Peterson >> > >> > -- >> > >> > Dave Peterson > > -- > > Dave Peterson
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: Save BeforeClose Next: Help changing Keyboard Shortcut assigned to Excel Macro |