Hi

I have an excel sheet that is used by many people (share workbook not an
option I want to use).

I have found code that allows me to force close the workbook after a certain
time. I want after one hour to prompt users (msgbox,vbYesNoCancel) to close
the workbook. This is fine. However I want after 2 hours to force close
regardless. I am missing one piece which is if a user goes home leaving the
workbook open the workbook will produce a prompt after one hour. Until an
option is selected the second piece of code (ie the force close after 2
hours) does not execute. What can I enter in code to ignore or cancel the
first prompt (msgbox) if a user doesnt action it?


--
Kev

Instead of a MsgBox, you could use a timed userform,

In the form have a label with the message and Yes and no buttons and this
code

Public Response As VbMsgBoxResult
Public Delay As Double

Private Sub cmdYes_Click()
Response = vbYes
Me.Hide
End Sub

Private Sub cmdNo_Click()
Response = vbNo
Me.Hide
End Sub

Private Sub UserForm_Activate()
nTime = Now + Delay
Application.OnTime nTime, "CloseForm"
End Sub

and then in a standard code module, add this code to launche this message
box

Public nTime As Double
Public frm As frmMessage

Public Sub ShowMessageForm()

Set frm = New frmMessage
frm.Delay = TimeSerial(0, 0, 10) 'adjust to suit
frm.Show
On Error Resume Next
Application.OnTime nTime, "CloseForm", , False
On Error GoTo 0
Unload frm

End Sub

Public Sub CloseForm()
If frm.Response = vbYes Then

ThisWorkbook.Save
ThisWorkbook.Close
Else

'your code to do something in another hour
End If
Unload frm
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Kevin" wrote in message
...
Hi

I have an excel sheet that is used by many people (share workbook not an
option I want to use).

I have found code that allows me to force close the workbook after a
certain
time. I want after one hour to prompt users (msgbox,vbYesNoCancel) to
close
the workbook. This is fine. However I want after 2 hours to force close
regardless. I am missing one piece which is if a user goes home leaving
the
workbook open the workbook will produce a prompt after one hour. Until an
option is selected the second piece of code (ie the force close after 2
hours) does not execute. What can I enter in code to ignore or cancel the
first prompt (msgbox) if a user doesnt action it?


--
Kev

is this because there is no simple way to ignore the msgbox prompt after a
certain time ? ie the code I currently have works fine except to the point
where the user does not action the prompt after 1 hour
--
Kevin


"Bob Phillips" wrote:

Instead of a MsgBox, you could use a timed userform,

In the form have a label with the message and Yes and no buttons and this
code

Public Response As VbMsgBoxResult
Public Delay As Double

Private Sub cmdYes_Click()
Response = vbYes
Me.Hide
End Sub

Private Sub cmdNo_Click()
Response = vbNo
Me.Hide
End Sub

Private Sub UserForm_Activate()
nTime = Now + Delay
Application.OnTime nTime, "CloseForm"
End Sub

and then in a standard code module, add this code to launche this message
box

Public nTime As Double
Public frm As frmMessage

Public Sub ShowMessageForm()

Set frm = New frmMessage
frm.Delay = TimeSerial(0, 0, 10) 'adjust to suit
frm.Show
On Error Resume Next
Application.OnTime nTime, "CloseForm", , False
On Error GoTo 0
Unload frm

End Sub

Public Sub CloseForm()
If frm.Response = vbYes Then

ThisWorkbook.Save
ThisWorkbook.Close
Else

'your code to do something in another hour
End If
Unload frm
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Kevin" wrote in message
...
Hi

I have an excel sheet that is used by many people (share workbook not an
option I want to use).

I have found code that allows me to force close the workbook after a
certain
time. I want after one hour to prompt users (msgbox,vbYesNoCancel) to
close
the workbook. This is fine. However I want after 2 hours to force close
regardless. I am missing one piece which is if a user goes home leaving
the
workbook open the workbook will produce a prompt after one hour. Until an
option is selected the second piece of code (ie the force close after 2
hours) does not execute. What can I enter in code to ignore or cancel the
first prompt (msgbox) if a user doesnt action it?


--
Kev

There is a technique, but I have found it to be very flaky, oft times the
message box just doesn't close down anyway.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Kevin" wrote in message
...
is this because there is no simple way to ignore the msgbox prompt after a
certain time ? ie the code I currently have works fine except to the point
where the user does not action the prompt after 1 hour
--
Kevin


"Bob Phillips" wrote:

Instead of a MsgBox, you could use a timed userform,

In the form have a label with the message and Yes and no buttons and this
code

Public Response As VbMsgBoxResult
Public Delay As Double

Private Sub cmdYes_Click()
Response = vbYes
Me.Hide
End Sub

Private Sub cmdNo_Click()
Response = vbNo
Me.Hide
End Sub

Private Sub UserForm_Activate()
nTime = Now + Delay
Application.OnTime nTime, "CloseForm"
End Sub

and then in a standard code module, add this code to launche this message
box

Public nTime As Double
Public frm As frmMessage

Public Sub ShowMessageForm()

Set frm = New frmMessage
frm.Delay = TimeSerial(0, 0, 10) 'adjust to suit
frm.Show
On Error Resume Next
Application.OnTime nTime, "CloseForm", , False
On Error GoTo 0
Unload frm

End Sub

Public Sub CloseForm()
If frm.Response = vbYes Then

ThisWorkbook.Save
ThisWorkbook.Close
Else

'your code to do something in another hour
End If
Unload frm
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Kevin" wrote in message
...
Hi

I have an excel sheet that is used by many people (share workbook not
an
option I want to use).

I have found code that allows me to force close the workbook after a
certain
time. I want after one hour to prompt users (msgbox,vbYesNoCancel) to
close
the workbook. This is fine. However I want after 2 hours to force close
regardless. I am missing one piece which is if a user goes home leaving
the
workbook open the workbook will produce a prompt after one hour. Until
an
option is selected the second piece of code (ie the force close after 2
hours) does not execute. What can I enter in code to ignore or cancel
the
first prompt (msgbox) if a user doesnt action it?


--
Kev