Application.ontime - still stuck :F

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Hiya,

I have a spreadsheet that looks like this:

pic.jpg


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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Ok, i'm a step further....

I've added the following code into the worksheet_selectionchange area in each worksheet in my workbook:

Code:
If Sheets("Callback").Range("L25").Value <= Now() Then MsgBox "Please call back " & Sheets("Callback").Range("B25")

However, this is far from ideal as:
1) It wont work in other applications, only when a user is in excel and
2) It wont work in other workbooks, only within my workbook, and when they click on a different cell.....

Please can anyone help me with this? Thanks
 

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
I've also come across a further problem. I can't get a date & time to concatinate into a tidy dd/mm/yyyy hh:mm format!

I have a calendar which would give me the dd/mm/yyyy part, and a cell that will hold the time value, but when I try to bring them together, Excel does not seem to store it correctly. Please can anyone help with this or the above please? Thanks
 

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453

ADVERTISEMENT

Please help need a hand :f
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Hi

Regarding the dates issue - if the date contains date alone (ie no time element) and the Time contains time alone (no date element) then you should simply be able to add the two together:

Range("A1").Value+Range("B1").Value

for example (and then format as required).

To get Excel to respond to events from another application is complicated and would presumably involve sub-classing/hooking into the Windows Messaging system. It is completely beyond me and I would think would need a professional VB developer to assist.
 

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Thanks Richard, that's helped with my date / time storage problem....

I'm hoping someone with knowledge of how excel / windows handles interrupts can help me!
 

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Hi guys,

I never actually resolved this. I've created a kind of workaround but it's not very good.....


I've put my code into a macro, and had the macro run whenever a user clicks any button, and put the macro into the worksheet_SelectionChange area of every worksheet, so it will work if the user clicks a button or changes a cell, but it will not come up automatically, and wont prompt with other applications or excel sheets.....

My macro code for info is:

Code:
If ThisWorkbook.Name <> "CRM 2.xls" Then Exit Sub
On Error Resume Next
If Sheets("Callback").Range("L23").Value = "" Then Exit Sub
If Sheets("Callback").Range("L23").Value > Now() Then Exit Sub
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 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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top