Public Variable problem
in [Programming]
|
From: ordnance1 on 22 May 2010 00:29 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 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
From: Dave Peterson on 22 May 2010 07:08 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
From: ordnance1 on 22 May 2010 12:44 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
From: Dave Peterson on 22 May 2010 14:36 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: ordnance1 on 22 May 2010 18:54 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
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: Save BeforeClose Next: Help changing Keyboard Shortcut assigned to Excel Macro |