We have a worksheet that is on a shared server. On occasion someone is
distracted and fails to close the worksheet, locking it up until our PC tech
comes in. As this spreadsheet is used in the U.S., Europe, and Asia, this has
created some serious issues.
Is there a way that we can force the spreadsheet closed, if no entry has
been made to it for 30 minutes?

This should get you started =)

Sub Auto_Open()
Application.OnTime Now() + TimeValue("00:30:00"), "CloseMe"
End Sub

Sub CloseMe()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

"Marie" wrote:

> We have a worksheet that is on a shared server. On occasion someone is
> distracted and fails to close the worksheet, locking it up until our PC tech
> comes in. As this spreadsheet is used in the U.S., Europe, and Asia, this has
> created some serious issues.
> Is there a way that we can force the spreadsheet closed, if no entry has
> been made to it for 30 minutes?

I should have elaborated a little more. Put the Sub Auto_Open into
ThisWorkbook, and the Sub CloseMe into a seperate module, and make it Public.
=)


Into Thisworkbook:

Sub Auto_Open()
Application.OnTime Now() + TimeValue("00:30:00"), "CloseMe"
End Sub

Into a new Module:

Public Sub CloseMe()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

THANK YOU! If this works (and I'm confident it will), you have no idea how
happy you will have made our Seattle office!

"Wood Grafing" wrote:

> This should get you started =)
>
> Sub Auto_Open()
> Application.OnTime Now() + TimeValue("00:30:00"), "CloseMe"
> End Sub
>
> Sub CloseMe()
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> End Sub
>
> "Marie" wrote:
>
> > We have a worksheet that is on a shared server. On occasion someone is
> > distracted and fails to close the worksheet, locking it up until our PC tech
> > comes in. As this spreadsheet is used in the U.S., Europe, and Asia, this has
> > created some serious issues.
> > Is there a way that we can force the spreadsheet closed, if no entry has
> > been made to it for 30 minutes?

That code does not look for inactivity. It closes the file after 30 minutes
regardless... Here is a slight modification of that code. It will pop up a
timed message box every (currently set to 20 seconds for degbugging but you
can change it to 30 minutes). The message box will stay up for 2 seconds. If
you hit yes then the 20 second (30 Minute) clock will start again. If not
then it saves and closes the file... Not you need to reference "Windows
Script Host Object Model" (in the VBE Tools -> References -> "Windows Script
Host Object Model"). This code sould be placed in a standard code module.

Sub Auto_Open()
Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _
Title:="Active", Type:=vbYesNo)
If Res = vbYes Then
Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe"
Else
ThisWorkbook.Save
ThisWorkbook.Close
End If
End Sub

--
HTH...

Jim Thomlinson


"Marie" wrote:

> THANK YOU! If this works (and I'm confident it will), you have no idea how
> happy you will have made our Seattle office!
>
> "Wood Grafing" wrote:
>
> > This should get you started =)
> >
> > Sub Auto_Open()
> > Application.OnTime Now() + TimeValue("00:30:00"), "CloseMe"
> > End Sub
> >
> > Sub CloseMe()
> > ActiveWorkbook.Save
> > ActiveWorkbook.Close
> > End Sub
> >
> > "Marie" wrote:
> >
> > > We have a worksheet that is on a shared server. On occasion someone is
> > > distracted and fails to close the worksheet, locking it up until our PC tech
> > > comes in. As this spreadsheet is used in the U.S., Europe, and Asia, this has
> > > created some serious issues.
> > > Is there a way that we can force the spreadsheet closed, if no entry has
> > > been made to it for 30 minutes?

That works great!
Would you happen to know the code to close the excel application, as well as
the spreadsheet?...
Thank you,
Marie

"Jim Thomlinson" wrote:

> That code does not look for inactivity. It closes the file after 30 minutes
> regardless... Here is a slight modification of that code. It will pop up a
> timed message box every (currently set to 20 seconds for degbugging but you
> can change it to 30 minutes). The message box will stay up for 2 seconds. If
> you hit yes then the 20 second (30 Minute) clock will start again. If not
> then it saves and closes the file... Not you need to reference "Windows
> Script Host Object Model" (in the VBE Tools -> References -> "Windows Script
> Host Object Model"). This code sould be placed in a standard code module.
>
> Sub Auto_Open()
> Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _
> Title:="Active", Type:=vbYesNo)
> If Res = vbYes Then
> Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe"
> Else
> ThisWorkbook.Save
> ThisWorkbook.Close
> End If
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Marie" wrote:
>
> > THANK YOU! If this works (and I'm confident it will), you have no idea how
> > happy you will have made our Seattle office!
> >
> > "Wood Grafing" wrote:
> >
> > > This should get you started =)
> > >
> > > Sub Auto_Open()
> > > Application.OnTime Now() + TimeValue("00:30:00"), "CloseMe"
> > > End Sub
> > >
> > > Sub CloseMe()
> > > ActiveWorkbook.Save
> > > ActiveWorkbook.Close
> > > End Sub
> > >
> > > "Marie" wrote:
> > >
> > > > We have a worksheet that is on a shared server. On occasion someone is
> > > > distracted and fails to close the worksheet, locking it up until our PC tech
> > > > comes in. As this spreadsheet is used in the U.S., Europe, and Asia, this has
> > > > created some serious issues.
> > > > Is there a way that we can force the spreadsheet closed, if no entry has
> > > > been made to it for 30 minutes?

Application.Quit

Careful with that one as there may be other books open when you execute the
code and you need to handle the other books in that case...
--
HTH...

Jim Thomlinson


"Marie" wrote:

> That works great!
> Would you happen to know the code to close the excel application, as well as
> the spreadsheet?...
> Thank you,
> Marie
>
> "Jim Thomlinson" wrote:
>
> > That code does not look for inactivity. It closes the file after 30 minutes
> > regardless... Here is a slight modification of that code. It will pop up a
> > timed message box every (currently set to 20 seconds for degbugging but you
> > can change it to 30 minutes). The message box will stay up for 2 seconds. If
> > you hit yes then the 20 second (30 Minute) clock will start again. If not
> > then it saves and closes the file... Not you need to reference "Windows
> > Script Host Object Model" (in the VBE Tools -> References -> "Windows Script
> > Host Object Model"). This code sould be placed in a standard code module.
> >
> > Sub Auto_Open()
> > Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _
> > Title:="Active", Type:=vbYesNo)
> > If Res = vbYes Then
> > Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe"
> > Else
> > ThisWorkbook.Save
> > ThisWorkbook.Close
> > End If
> > End Sub
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Marie" wrote:
> >
> > > THANK YOU! If this works (and I'm confident it will), you have no idea how
> > > happy you will have made our Seattle office!
> > >
> > > "Wood Grafing" wrote:
> > >
> > > > This should get you started =)
> > > >
> > > > Sub Auto_Open()
> > > > Application.OnTime Now() + TimeValue("00:30:00"), "CloseMe"
> > > > End Sub
> > > >
> > > > Sub CloseMe()
> > > > ActiveWorkbook.Save
> > > > ActiveWorkbook.Close
> > > > End Sub
> > > >
> > > > "Marie" wrote:
> > > >
> > > > > We have a worksheet that is on a shared server. On occasion someone is
> > > > > distracted and fails to close the worksheet, locking it up until our PC tech
> > > > > comes in. As this spreadsheet is used in the U.S., Europe, and Asia, this has
> > > > > created some serious issues.
> > > > > Is there a way that we can force the spreadsheet closed, if no entry has
> > > > > been made to it for 30 minutes?

Perfect. Many Thanks.
marie

"Jim Thomlinson" wrote:

> Application.Quit
>
> Careful with that one as there may be other books open when you execute the
> code and you need to handle the other books in that case...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Marie" wrote:
>
> > That works great!
> > Would you happen to know the code to close the excel application, as well as
> > the spreadsheet?...
> > Thank you,
> > Marie
> >
> > "Jim Thomlinson" wrote:
> >
> > > That code does not look for inactivity. It closes the file after 30 minutes
> > > regardless... Here is a slight modification of that code. It will pop up a
> > > timed message box every (currently set to 20 seconds for degbugging but you
> > > can change it to 30 minutes). The message box will stay up for 2 seconds. If
> > > you hit yes then the 20 second (30 Minute) clock will start again. If not
> > > then it saves and closes the file... Not you need to reference "Windows
> > > Script Host Object Model" (in the VBE Tools -> References -> "Windows Script
> > > Host Object Model"). This code sould be placed in a standard code module.
> > >
> > > Sub Auto_Open()
> > > Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _
> > > Title:="Active", Type:=vbYesNo)
> > > If Res = vbYes Then
> > > Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe"
> > > Else
> > > ThisWorkbook.Save
> > > ThisWorkbook.Close
> > > End If
> > > End Sub
> > >
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Marie" wrote:
> > >
> > > > THANK YOU! If this works (and I'm confident it will), you have no idea how
> > > > happy you will have made our Seattle office!
> > > >
> > > > "Wood Grafing" wrote:
> > > >
> > > > > This should get you started =)
> > > > >
> > > > > Sub Auto_Open()
> > > > > Application.OnTime Now() + TimeValue("00:30:00"), "CloseMe"
> > > > > End Sub
> > > > >
> > > > > Sub CloseMe()
> > > > > ActiveWorkbook.Save
> > > > > ActiveWorkbook.Close
> > > > > End Sub
> > > > >
> > > > > "Marie" wrote:
> > > > >
> > > > > > We have a worksheet that is on a shared server. On occasion someone is
> > > > > > distracted and fails to close the worksheet, locking it up until our PC tech
> > > > > > comes in. As this spreadsheet is used in the U.S., Europe, and Asia, this has
> > > > > > created some serious issues.
> > > > > > Is there a way that we can force the spreadsheet closed, if no entry has
> > > > > > been made to it for 30 minutes?

Must Application.OnTime xxxx always execute a macro?
Can I just have wait a specified period of time and then do nothing? in
other words I just want a loop that waits a while.
I know I can create a do nothing macro but thought I can be more elegant
than that.

Also where can I find an explanation of the full syntax of Application.OnTime?

Thanks.

"Marie" wrote:

> Perfect. Many Thanks.
> marie
>
> "Jim Thomlinson" wrote:
>
> > Application.Quit
> >
> > Careful with that one as there may be other books open when you execute the
> > code and you need to handle the other books in that case...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Marie" wrote:
> >
> > > That works great!
> > > Would you happen to know the code to close the excel application, as well as
> > > the spreadsheet?...
> > > Thank you,
> > > Marie
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > That code does not look for inactivity. It closes the file after 30 minutes
> > > > regardless... Here is a slight modification of that code. It will pop up a
> > > > timed message box every (currently set to 20 seconds for degbugging but you
> > > > can change it to 30 minutes). The message box will stay up for 2 seconds. If
> > > > you hit yes then the 20 second (30 Minute) clock will start again. If not
> > > > then it saves and closes the file... Not you need to reference "Windows
> > > > Script Host Object Model" (in the VBE Tools -> References -> "Windows Script
> > > > Host Object Model"). This code sould be placed in a standard code module.
> > > >
> > > > Sub Auto_Open()
> > > > Application.OnTime Now() + TimeValue("00:00:20"), "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?", secondstowait:=2, _
> > > > Title:="Active", Type:=vbYesNo)
> > > > If Res = vbYes Then
> > > > Application.OnTime Now() + TimeValue("00:00:20"), "CloseMe"
> > > > Else
> > > > ThisWorkbook.Save
> > > > ThisWorkbook.Close
> > > > End If
> > > > End Sub
> > > >
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Marie" wrote:
> > > >
> > > > > THANK YOU! If this works (and I'm confident it will), you have no idea how
> > > > > happy you will have made our Seattle office!
> > > > >
> > > > > "Wood Grafing" wrote:
> > > > >
> > > > > > This should get you started =)
> > > > > >
> > > > > > Sub Auto_Open()
> > > > > > Application.OnTime Now() + TimeValue("00:30:00"), "CloseMe"
> > > > > > End Sub
> > > > > >
> > > > > > Sub CloseMe()
> > > > > > ActiveWorkbook.Save
> > > > > > ActiveWorkbook.Close
> > > > > > End Sub
> > > > > >
> > > > > > "Marie" wrote:
> > > > > >
> > > > > > > We have a worksheet that is on a shared server. On occasion someone is
> > > > > > > distracted and fails to close the worksheet, locking it up until our PC tech
> > > > > > > comes in. As this spreadsheet is used in the U.S., Europe, and Asia, this has
> > > > > > > created some serious issues.
> > > > > > > Is there a way that we can force the spreadsheet closed, if no entry has
> > > > > > > been made to it for 30 minutes?