Create a pop up

[dvuppx]

i have a document that is shared by 5 different people in our office, and often people forget to leave it open. Im struggling to write a macro that would make a pop up appear every 15 minutes to remind people to close the document if they are finished editing it. any suggestions?

 

[gruntingmonkey]

Re: how to create a pop up

The below should work:

Private Sub Workbook_Open()
  
Application.ontime Now + TimeValue("00:15:00"), "TimeMsg"
MsgBox "Please remember to close this workbook"
 
End Sub

You will need to put it in the Thisworkbook object so it starts running when you open the file

 

[wideboydixon]

Re: how to create a pop up

Create a new module in VBA and paste the following:

Private scheduledToRun As Boolean
Private runTime As Variant
Public Sub SheetChange()

Call CancelMacro
runTime = Now + TimeSerial(0, 15, 0)
Application.OnTime EarliestTime:=runTime, Procedure:="CloseReminder"
scheduledToRun = True

End Sub
Public Sub CloseReminder()

scheduledToRun = False
If MsgBox("You've been inactive for 15 minutes. Do you need to close the spreadsheet?", vbQuestion + vbYesNo) = vbYes Then
    ActiveWorkbook.Close
Else
    Call SheetChange
End If

End Sub
Public Sub CancelMacro()

If scheduledToRun Then Application.OnTime EarliestTime:=runTime, Procedure:="CloseReminder", Schedule:=False

End Sub

Now select the "ThisWorkbook" module and paste the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call CancelMacro

End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Call SheetChange

End Sub

WBD