Public Variable problem
in [Programming]
|
From: Dave Peterson on 24 May 2010 08:06 I don't understand why you have these lines of code: > If ActiveWorkbook.ReadOnly = True Then > ThisWorkbook.Close False > Else > ThisWorkbook.Close True > End If In this sub. =========== I think I would drop your current code and visit Chip Pearson's site: http://www.cpearson.com/excel/TimedClose.htm But his code wants to close and save the workbook. So you'll have to change this procedure from: Public Sub SaveAndClose() ThisWorkbook.Close savechanges:=True End Sub to Public Sub SaveAndClose() ThisWorkbook.Close 'savechanges:=True End Sub And you'll want to modify his _Beforeclose event to include your rules: Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 End Sub becomes Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.OnTime RunWhen, "SaveAndClose", , False On Error GoTo 0 If me.readonly = true Then me.saved = true ' a white lie to excel else 'stop any other events from firing application.enableevents = false me.save application.enableevents = true end if End Sub I don't know how/where you used that NoSave variable. ordnance1 wrote: > > 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 -- Dave Peterson
From: ordnance1 on 24 May 2010 12:48 They are there so that at the end of the timer period (in this case 15 minutes), if the file was opened as read only the file will close without saving changes, if the file is not read only then changes will be saved upon closing. Since this workbook is accessed by many people, the timer is there to prevent the workbook from being left open on someone's desk top (and unavailable to others) for an extended period of time. But I will follow your advise and check out Chip Pearsons site. I do want to thank you for the time and expertise you have offered. "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message news:4BFA6BCB.43A840B5(a)verizonXSPAM.net... > I don't understand why you have these lines of code: > >> If ActiveWorkbook.ReadOnly = True Then >> ThisWorkbook.Close False >> Else >> ThisWorkbook.Close True >> End If > > In this sub. > > =========== > I think I would drop your current code and visit Chip Pearson's site: > http://www.cpearson.com/excel/TimedClose.htm > > But his code wants to close and save the workbook. So you'll have to > change > this procedure from: > > Public Sub SaveAndClose() > ThisWorkbook.Close savechanges:=True > End Sub > > to > > Public Sub SaveAndClose() > ThisWorkbook.Close 'savechanges:=True > End Sub > > And you'll want to modify his _Beforeclose event to include your rules: > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > On Error Resume Next > Application.OnTime RunWhen, "SaveAndClose", , False > On Error GoTo 0 > End Sub > > becomes > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > On Error Resume Next > Application.OnTime RunWhen, "SaveAndClose", , False > On Error GoTo 0 > > If me.readonly = true Then > me.saved = true ' a white lie to excel > else > 'stop any other events from firing > application.enableevents = false > me.save > application.enableevents = true > end if > > End Sub > > I don't know how/where you used that NoSave variable. > > ordnance1 wrote: >> >> 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 > > -- > > Dave Peterson
From: Dave Peterson on 24 May 2010 13:26 But you're not testing anything before those lines execute. So you'd be closing the workbook (saved or unsaved) when that portion of the code ran. Besides that, the code wouldn't compile for me. There was no label named Finished (for example). ordnance1 wrote: > > They are there so that at the end of the timer period (in this case 15 > minutes), if the file was opened as read only the file will close without > saving changes, if the file is not read only then changes will be saved upon > closing. Since this workbook is accessed by many people, the timer is there > to prevent the workbook from being left open on someone's desk top (and > unavailable to others) for an extended period of time. But I will follow > your advise and check out Chip Pearsons site. > > I do want to thank you for the time and expertise you have offered. > > "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message > news:4BFA6BCB.43A840B5(a)verizonXSPAM.net... > > I don't understand why you have these lines of code: > > > >> If ActiveWorkbook.ReadOnly = True Then > >> ThisWorkbook.Close False > >> Else > >> ThisWorkbook.Close True > >> End If > > > > In this sub. > > > > =========== > > I think I would drop your current code and visit Chip Pearson's site: > > http://www.cpearson.com/excel/TimedClose.htm > > > > But his code wants to close and save the workbook. So you'll have to > > change > > this procedure from: > > > > Public Sub SaveAndClose() > > ThisWorkbook.Close savechanges:=True > > End Sub > > > > to > > > > Public Sub SaveAndClose() > > ThisWorkbook.Close 'savechanges:=True > > End Sub > > > > And you'll want to modify his _Beforeclose event to include your rules: > > > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > > On Error Resume Next > > Application.OnTime RunWhen, "SaveAndClose", , False > > On Error GoTo 0 > > End Sub > > > > becomes > > > > Private Sub Workbook_BeforeClose(Cancel As Boolean) > > On Error Resume Next > > Application.OnTime RunWhen, "SaveAndClose", , False > > On Error GoTo 0 > > > > If me.readonly = true Then > > me.saved = true ' a white lie to excel > > else > > 'stop any other events from firing > > application.enableevents = false > > me.save > > application.enableevents = true > > end if > > > > End Sub > > > > I don't know how/where you used that NoSave variable. > > > > ordnance1 wrote: > >> > >> 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 > > > > -- > > > > Dave Peterson -- Dave Peterson
From: ordnance1 on 24 May 2010 14:26 That code ran at the end of the timer. But I checked out the site you recommended and was able to incorporate Mr. Pearsons code (removed my timer code) and everything is working flawlessly. Again I want to thank you for all your help. "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message news:4BFAB6DF.B7A698E9(a)verizonXSPAM.net... > But you're not testing anything before those lines execute. > > So you'd be closing the workbook (saved or unsaved) when that portion of > the > code ran. > > Besides that, the code wouldn't compile for me. There was no label named > Finished (for example). > > ordnance1 wrote: >> >> They are there so that at the end of the timer period (in this case 15 >> minutes), if the file was opened as read only the file will close without >> saving changes, if the file is not read only then changes will be saved >> upon >> closing. Since this workbook is accessed by many people, the timer is >> there >> to prevent the workbook from being left open on someone's desk top (and >> unavailable to others) for an extended period of time. But I will follow >> your advise and check out Chip Pearsons site. >> >> I do want to thank you for the time and expertise you have offered. >> >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message >> news:4BFA6BCB.43A840B5(a)verizonXSPAM.net... >> > I don't understand why you have these lines of code: >> > >> >> If ActiveWorkbook.ReadOnly = True Then >> >> ThisWorkbook.Close False >> >> Else >> >> ThisWorkbook.Close True >> >> End If >> > >> > In this sub. >> > >> > =========== >> > I think I would drop your current code and visit Chip Pearson's site: >> > http://www.cpearson.com/excel/TimedClose.htm >> > >> > But his code wants to close and save the workbook. So you'll have to >> > change >> > this procedure from: >> > >> > Public Sub SaveAndClose() >> > ThisWorkbook.Close savechanges:=True >> > End Sub >> > >> > to >> > >> > Public Sub SaveAndClose() >> > ThisWorkbook.Close 'savechanges:=True >> > End Sub >> > >> > And you'll want to modify his _Beforeclose event to include your rules: >> > >> > Private Sub Workbook_BeforeClose(Cancel As Boolean) >> > On Error Resume Next >> > Application.OnTime RunWhen, "SaveAndClose", , False >> > On Error GoTo 0 >> > End Sub >> > >> > becomes >> > >> > Private Sub Workbook_BeforeClose(Cancel As Boolean) >> > On Error Resume Next >> > Application.OnTime RunWhen, "SaveAndClose", , False >> > On Error GoTo 0 >> > >> > If me.readonly = true Then >> > me.saved = true ' a white lie to excel >> > else >> > 'stop any other events from firing >> > application.enableevents = false >> > me.save >> > application.enableevents = true >> > end if >> > >> > End Sub >> > >> > I don't know how/where you used that NoSave variable. >> > >> > ordnance1 wrote: >> >> >> >> 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 >> > >> > -- >> > >> > Dave Peterson > > -- > > Dave Peterson
From: Dave Peterson on 24 May 2010 15:00 I don't understand how the old code ever worked, but that's not important now. Glad you got the new code working the way you want. ordnance1 wrote: > > That code ran at the end of the timer. But I checked out the site you > recommended and was able to incorporate Mr. Pearsons code (removed my timer > code) and everything is working flawlessly. Again I want to thank you for > all your help. > > "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message > news:4BFAB6DF.B7A698E9(a)verizonXSPAM.net... > > But you're not testing anything before those lines execute. > > > > So you'd be closing the workbook (saved or unsaved) when that portion of > > the > > code ran. > > > > Besides that, the code wouldn't compile for me. There was no label named > > Finished (for example). > > > > ordnance1 wrote: > >> > >> They are there so that at the end of the timer period (in this case 15 > >> minutes), if the file was opened as read only the file will close without > >> saving changes, if the file is not read only then changes will be saved > >> upon > >> closing. Since this workbook is accessed by many people, the timer is > >> there > >> to prevent the workbook from being left open on someone's desk top (and > >> unavailable to others) for an extended period of time. But I will follow > >> your advise and check out Chip Pearsons site. > >> > >> I do want to thank you for the time and expertise you have offered. > >> > >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message > >> news:4BFA6BCB.43A840B5(a)verizonXSPAM.net... > >> > I don't understand why you have these lines of code: > >> > > >> >> If ActiveWorkbook.ReadOnly = True Then > >> >> ThisWorkbook.Close False > >> >> Else > >> >> ThisWorkbook.Close True > >> >> End If > >> > > >> > In this sub. > >> > > >> > =========== > >> > I think I would drop your current code and visit Chip Pearson's site: > >> > http://www.cpearson.com/excel/TimedClose.htm > >> > > >> > But his code wants to close and save the workbook. So you'll have to > >> > change > >> > this procedure from: > >> > > >> > Public Sub SaveAndClose() > >> > ThisWorkbook.Close savechanges:=True > >> > End Sub > >> > > >> > to > >> > > >> > Public Sub SaveAndClose() > >> > ThisWorkbook.Close 'savechanges:=True > >> > End Sub > >> > > >> > And you'll want to modify his _Beforeclose event to include your rules: > >> > > >> > Private Sub Workbook_BeforeClose(Cancel As Boolean) > >> > On Error Resume Next > >> > Application.OnTime RunWhen, "SaveAndClose", , False > >> > On Error GoTo 0 > >> > End Sub > >> > > >> > becomes > >> > > >> > Private Sub Workbook_BeforeClose(Cancel As Boolean) > >> > On Error Resume Next > >> > Application.OnTime RunWhen, "SaveAndClose", , False > >> > On Error GoTo 0 > >> > > >> > If me.readonly = true Then > >> > me.saved = true ' a white lie to excel > >> > else > >> > 'stop any other events from firing > >> > application.enableevents = false > >> > me.save > >> > application.enableevents = true > >> > end if > >> > > >> > End Sub > >> > > >> > I don't know how/where you used that NoSave variable. > >> > > >> > ordnance1 wrote: > >> >> > >> >> 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 > >> > > >> > -- > >> > > >> > Dave Peterson > > > > -- > > > > Dave Peterson -- Dave Peterson
First
|
Prev
|
Pages: 1 2 3 4 Prev: Save BeforeClose Next: Help changing Keyboard Shortcut assigned to Excel Macro |