Application.ontime - still stuck :F

thorpyuk · Jul 31, 2007

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:

Code:

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

Please can anyone help? Thanks

Legacy 98055 · Sep 4, 2007

"bumpety bump"?

How exactly do these dates and times find their way into column L? I would assume that there must be a dynamic-dependant structure if this can occur even when the user is not actively using that particular worksheet. Correct? Please explain "all" of the ways dates/times are stamped into this worksheet in column L. This is where I would look first. You may be able to cleanly resond to level 1 events instead of looping.

Also, what do you mean by "I want to bring up a prompt whenever now() is equal to any of the date / time combinations in column L"? I think you are speaking of a messagebox?... If not, please define what this prompt is.

What behavior are you looking for when a (Now = DateTime) event occurs and Excel is not the active application? Should Excel become the foreground window? What behavior are you looking for here?

thorpyuk · Sep 4, 2007

Hi right_click, thanks for replying....

Basically, it's a callback cue, and the date / time gets put into column L by the user adding a callback time and date, and clicking 'add'

I then sort column L, so always the next time/date to need monitoring is Cell L23..... because this is the nearest callback time to now.

If excel, and even better, my workbook "CRM 2" were to become the active window, and a msgbox were to appear, following my code as is now:

This is the code which checks the cell at the moment and brings in the msgbox:

Code:

If Sheets("Callback").Range("L23").Value <= Now() Then MsgBox "Please call back " & Sheets("Callback").Range("B23") & " from " & Sheets("Callback").Range("C23").Value & " on " & Sheets("Callback").Range("K23").Value

Then this is the rest of the macro, which makes the callback sheet active, asks the user to remove the event from the queue (eg line 23) and then returns the user to wverever they were in the spreadsheet before.

Code:

If Sheets("Callback").Range("L23").Value <= Now() Then Sheets("Callback").Visible = True
Set returnsheet = ActiveSheet
Sheets("Callback").Select
   Dim Sheet As Object
   For Each Sheet In Sheets
       If Not Sheet.Name = "Callback" Then
           Sheet.Visible = xlSheetVeryHidden
       End If
   Next
   Set Sheet = Nothing
    Ans = MsgBox("Do you wish to remove this appointment from the callback queue?", 4, "CRM Callback")
        If Ans = vbYes Then
            Range("A23:L23").ClearContents
            Range("A22:L40").Select
            Selection.Sort Key1:=Range("L23"), Order1:=xlAscending, Header:=xlGuess, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            Range("C12").Select
            returnsheet.Visible = True
            returnsheet.Select
            Sheets("Callback").Visible = False
            Exit Sub
        Else: Exit Sub
        End If

Legacy 98055 · Sep 4, 2007

Start with this...

<table border="1" bgcolor="White"><caption ALIGN=left>Example VBA Code:</caption><tr><td> Option Explicit

 Private Declare Function GetForegroundWindow Lib "user32" () As Long
 Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long

 Private RunNextTime As Date

 Private Sub Workbook_BeforeClose(Cancel As Boolean)
 CancelOnTime
 End Sub

 Private Sub Workbook_Open()
 SchdlOnTime
 End Sub

 Private Sub CallBackProcessor()

 Dim Sheet As Object, returnsheet As Object, Ans As VbMsgBoxResult

 If Sheets("Callback").Range("L23").Value <= Now() Then
 'place your code here or call your procedure from here

 Sheets("Callback").Visible = True
 Set returnsheet = ActiveSheet
 Sheets("Callback").Select

 If Application.WindowState = xlMinimized Then Application.WindowState = xlMaximized

 If GetForegroundWindow <> Application.hwnd Then
 SetForegroundWindow Application.hwnd
 End If

 For Each Sheet In Sheets
 If Not Sheet.Name = "Callback" Then
 Sheet.Visible = xlSheetVeryHidden
 End If
 Next

 Set Sheet = Nothing

 Ans = MsgBox("Do you wish to remove this appointment from the callback queue?", vbYesNo, "CRM Callback")

 If Ans = vbYes Then
 Range("A23:L23").ClearContents
 Range("A22:L40").Select
 Selection.Sort Key1:=Range("L23"), Order1:=xlAscending, Header:=xlGuess, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
 Range("C12").Select
 returnsheet.Visible = True
 returnsheet.Select
 Sheets("Callback").Visible = False
 End If

 End If

 SchdlOnTime
 End Sub

 Private Sub SchdlOnTime()
 RunNextTime = Now + TimeSerial(0, 0, 1)
 Application.OnTime RunNextTime, "ThisWorkbook.CallBackProcessor"
 End Sub

 Private Sub CancelOnTime()
 Application.OnTime RunNextTime, "ThisWorkbook.CallBackProcessor", False
 End Sub
</td></tr></table>

thorpyuk · Sep 4, 2007

nearly right_click, I got a run-time error in:

Code:

  Private Sub Workbook_BeforeClose(Cancel As Boolean) 
       CancelOnTime 
  End Sub

"sub or function not defined", and the same with:

Code:

Private Sub Workbook_Open()
      SchdlOnTime
End Sub

I was unsure about where I should put all the code. The beforeclose and workbook_open were easy enough (although I have code in them already so I couldnt just copy & paste the lot.....)

But the rest, I pasted into a module... is this correct?
Thanks!

thorpyuk · Sep 4, 2007

Ok, I sorted it out, and it works, but not without some quite major side effects!!!

Firstly, it wont allow me to edit anything using the control toolbar, because it switches the focus off for eg, a hidden command button too quickly. I suspect this may be solved by the use of the time serial....

Secondly, it wont let me close the thing down lol! Every time I shut the workbook down, it opens it right up again from the start :f

Here's the code as I have it under callbackprocessor:

Code:

  Private Sub CallBackProcessor()
       
       Dim Sheet As Object, returnsheet As Object, Ans As VbMsgBoxResult
       If Sheets("Callback").Range("L23").Text = "" Then GoTo 80
       If Sheets("Callback").Range("L23").Value <= Now() Then MsgBox "Please call back " & Sheets("Callback").Range("B23") & " from " & Sheets("Callback").Range("C23").Value & " on " & Sheets("Callback").Range("K23").Value
       If Sheets("Callback").Range("L23").Value <= Now() Then
           'place your code here or call your procedure from here
           
           Sheets("Callback").Visible = True
           Set returnsheet = ActiveSheet
           Sheets("Callback").Select
           
           If Application.WindowState = xlMinimized Then Application.WindowState = xlMaximized
           
           If GetForegroundWindow <> Application.hwnd Then
               SetForegroundWindow Application.hwnd
           End If
           
           For Each Sheet In Sheets
               If Not Sheet.Name = "Callback" Then
                   Sheet.Visible = xlSheetVeryHidden
               End If
           Next
           
           Set Sheet = Nothing
           
           Ans = MsgBox("Do you wish to remove this appointment from the callback queue?", vbYesNo, "CRM Callback")
           
           If Ans = vbYes Then
               Range("A23:L23").ClearContents
               Range("A22:L40").Select
               Selection.Sort Key1:=Range("L23"), Order1:=xlAscending, Header:=xlGuess, _
                   OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
               Range("C12").Select
               returnsheet.Visible = True
               returnsheet.Select
               Sheets("Callback").Visible = False
           End If
           
       End If
       
80  SchdlOnTime
  End Sub

thorpyuk · Sep 4, 2007

*bumpety bump*

thorpyuk · Sep 5, 2007

*bumpety bump*

Does anyone know why this causes my spreadsheet to automatically re-open again every time?

thorpyuk · Sep 5, 2007

*hoist*

thorpyuk · Sep 5, 2007

*boost*

Legacy 98055 · Sep 5, 2007

i won't have time to reply until late pm wed

Application.ontime - still stuck :F

thorpyuk

Well-known Member

Legacy 98055

Guest

Excel Facts

thorpyuk

Well-known Member

Legacy 98055

Guest

thorpyuk

Well-known Member

thorpyuk

Well-known Member

thorpyuk

Well-known Member

thorpyuk

Well-known Member

thorpyuk

Well-known Member

thorpyuk

Well-known Member

Legacy 98055

Guest

Similar threads

Forum statistics

Share this page

Application.ontime - still stuck :F

Well-known Member

Legacy 98055

Guest

Excel Facts

Well-known Member

Legacy 98055

Guest

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Legacy 98055

Guest

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock