Application.ontime - still stuck :F

[thorpyuk]

Hiya,

I have a spreadsheet that looks like this:

I want to bring up a prompt whenever now() is equal to any of the date / time combinations in column L, regardless of where in the spreadsheet I am working. (or even better, regardless of which application I am working in). The spreadsheet will always be open...

I've tried this code in the workbook_SheetSelectionChange module, but it does not work & gives me an error:

If Application.OnTime = Sheets("Callback").Range("L25").Value Then
    MsgBox "Please contact " & Sheets("Callback").Range("B25").Value

Please can anyone help? Thanks

 

[thorpyuk]

*hoik*

Ok, no problem. It's not a biggie, because you can close it by shutting excel down, as opposed to just the individual spreadsheet....

 

[thorpyuk]

*bounce*


If you're reading this for the 1st time, I have some code that does a wonderful job of bringing up a message box based upon a time I specify, but I now have the problem that excel wont close if I click the 'close spreadsheet' button, as it re-opens again!

 

[JazzSP8]

  Private Sub Workbook_BeforeClose(Cancel As Boolean) 
       CancelOnTime 
  End Sub

Private Sub Workbook_Open()
      SchdlOnTime
End Sub

Hi

If you've got CancelOnTime in Module 1 then try changing the code to;

Module1.CancelOnTime

And

Module1.SchdlOnTime

Not saying it will help, but you said (somewhere) that part failed.

Your Workbook is probably reopening because an Ontime event is being fired which would cause it to reopen, you need to make sure that the events are cancelled in the Workbook_BeforeClose section.

 

[thorpyuk]

Hi JazzSP8,

No, it's all within the workbook module...

This is the code for cancelontime:

Private Sub CancelOnTime()
       Application.OnTime RunNextTime, "ThisWorkbook.CallBackProcessor", False
  End Sub

This is the code for workbook_beforeclose:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
CancelOnTime
   With Application
       .EnableCancelKey = xlDisabled
       .ScreenUpdating = False
        
       Call HideSheets
        
       .ScreenUpdating = True
       .EnableCancelKey = xlInterrupt
   End With

End Sub

The other 'blurb' is based on someones code for forcing people to enable macros, but cancelontime should run before that, so it shouldn't have any effect...