I went to the timed closing of a workbook at thsis site and put in the
follwoing code but I keep getting a sub or function not defined at the
workbook_open, workbook_close.Is there an addin that I need to have or what
can I do to get it to work? If this works it will eliminate my other issue.
In a standard module of the VBA project, paste the following code:
Public RunWhen As Double
Public Const NUM_MINUTES = 10
Public Sub SaveAndClose()
ThisWorkbook.Close savechanges:=True
End Sub
Change the value of NUM_MINUTES to the number of minutes you want to leave
the workbook unattended before closing.
In the ThisWorkbook module, paste the following code:
Private Sub Workbook_Open()
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
RunWhen = Now + TimeSerial(0, NUM_MINUTES, 0)
Application.OnTime RunWhen, "SaveAndClose", , True
End Sub
"Gord Dibben" wrote:
> I think you have to stop the timer.
>
> See Chip Pearson's site for hints, tips and code for OnTime
>
> http://www.cpearson.com/excel/OnTime.aspx
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 1 Oct 2009 11:59:03 -0700, jtfalk <(E-Mail Removed)>
> wrote:
>
> >Sorry, I forgot to add that on close thisis the code - so somewhere here I
> >need to stop the timing for the public sub Closeme():
> >
> >Private Sub Workbook_BeforeClose(Cancel As Boolean)
> >Application.ScreenUpdating = False
> >
> > Sheets("home").Visible = xlSheetVeryHidden
> > Sheets("MACROS").Select
> >
> >
> >Application.ScreenUpdating = True
> >ThisWorkbook.Save
> >ThisWorkbook.Close
> >End Sub
> >
> >
> >"FSt1" wrote:
> >
> >> hi
> >> try putting an Exit Sub after ThisWorkbook.Close
> >>
> >> Regards
> >> FSt1
> >>
> >> "jtfalk" wrote:
> >>
> >> > i have a file that I have put in an auto-open on that asks if you are there
> >> > after 10 minutes, if you do not click yes it saves and closes the workbook
> >> > but not excel (incase there is another workbook open). So the problem is if
> >> > there are 2 or 3 workbooks open if I close the workbook myslf after 10
> >> > minutes it actually reopens the disable/enable macro box and then will show
> >> > my yes/no are you there box. On close what can I put in to shut off all
> >> > macro's in the workbook?
> >> > On close I need to STOP the Application.OnTime Now() +
> >> > TimeValue("00:00:50"), "CloseMe"
> >> >
> >> > Sub Auto_Open()
> >> > LogInformation ThisWorkbook.Name & " opened by " & Application.UserName &
> >> > " " & Format(Date, "yyyy-mm-dd") & " " & Format(Time, "hh:mm")
> >> > Application.OnTime Now() + TimeValue("00:00:50"), "CloseMe"
> >> > End Sub
> >> > 'requires reference to "Windows Script Host Object Model"
> >> > Public Sub CloseMe()
> >> > Dim SH As IWshRuntimeLibrary.WshShell
> >> > Dim Res As Long
> >> >
> >> > Set SH = New IWshRuntimeLibrary.WshShell
> >> > Res = SH.Popup(Text:="Are you still there? Please click Yes or this file
> >> > will close in 2 minutes", secondstowait:=2, _
> >> > Title:="Active", Type:=vbYesNo)
> >> > If Res = vbYes Then
> >> > Application.OnTime Now() + TimeValue("00:00:50"), "CloseMe"
> >> > Else
> >> > ThisWorkbook.Save
> >> > ThisWorkbook.Close
> >> > End If
> >> > End Sub
>
>
Similar Threads
