VB - ignore a msgbox prompt

[Kevin]

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?

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)