How to use cell reference for TimeValue in VBA

[loloelpalomero]

Hello all. I would swear that I have seen this kind of syntax for ontime somewhere:

Application.OnTime TimeValue([A1]), "colorbg_macro"

But if I try to use it, a Type Mismatch error 13 appears. If I use a real time value like next one the function works:

Application.OnTime TimeValue("13:10:00"), "colorbg_macro"

I am trying to use cell values for drawing a bar while time increases. Times are in cells and I would like to use the cells references as values for TimeValue.

I have been searching in the forum but I haven't found a similar case.

Help would be appreciated.

Thanks in advanced.

 

[pbornemeier]

This seemed to work:

Application.OnTime TimeValue(CStr(Format([A1].Value, "hh:mm:ss"))), "colorbg_macro"

 

[mikerickson]

TimeValue takes a String as its argument. Excel serial times, no matter how the cell is formatted, are read as type Double.

Application.OnTime TimeValue(Range("A1").Text), "colorbg_macro"

will account for those users who use the 1904 date system.

 

[loloelpalomero]

Hi again. I have finally used mikerickson's option, as I needed a code wich don't needed to enter a string. It works!

Thanks to both for your replies.

Learning Excel step by step thanks to people like you...

 

[jim may]

How would someone use the below code? In what code window (Std, or event-type?)
What lines of code would proceed or follow the below? example, please..??

Application.OnTime TimeValue(Range("A1").Text), "colorbg_macro"

 

[loloelpalomero]

Hi Jim. I have put this part in Workbook VBA:

Private Sub Workbook_Open()
    Application.OnTime TimeValue(Range("D3").Text), "colorbg03"
End Sub

The previous code could be entered in other class, like Worksheet_Change or something. It depends on when you would prefer to start the code to be executed (check the time).

And this in a Module:

Sub colorbg03()
Range("d3:f3").Interior.Color = RGB(255, 0, 0)
End Sub

In cell D3 there is a time string (i.e.: "13:10:00")

When D3 time is reached, cells D3 to F3 background turn to red.

I am using this code for some kind of schedulling system but I don't like alarms, message boxes and things like that. I prefer just a bar growing through cells to be easy to find the "to-do" things. Bar is growing horizontally and "to-do" things are just below in a few rows.

Hope it helps.

 

[jim may]

When I open file with

Private Sub Workbook_Open()
Application.OnTime TimeValue(Range("A1").Text)
MsgBox "Hello - It's time"
'Application.OnTime TimeValue(Range("A1").Text), "my_Procedure"
End Sub

".OnTime" is highlited and I get error Compile error - Argument Not Optional

Why is that?

 

[pbornemeier]

The error occurred because there was no procedure inlcuded. This gives error:

Application.OnTime TimeValue(Range("A1").Text)

This does not:

Application.OnTime TimeValue(Range("A1").Text), "my_Procedure"

 

[loloelpalomero]

I am not sure what you are trying to do. I suppose that you want to show a message when time reached. If that is right then you will have to do the following:

(Be aware that I am a newbie in excel so do at your own risk Anyway, I have tested it and works...

1) Insert a time in A1 like 13:10:00

2) Open VBA Editor and, in the project window, double click in "This Workbook" and type in the window:

Private Sub Workbook_Open()
Application.OnTime TimeValue(Range("A1").Text), "my_Procedure"
End Sub

3) Open a module or create a new one and type:

Sub my_Procedure()
MsgBox "Hello - It's time"
End Sub

Remember that you have choosen a Workbook_Open procedure so you will have to close and re-open your excel file for activating.

 

[Lplater]

Thought this code was my saviour but finding it's very hit and miss as to whether the time applied in my code will trigger my macros.
Would really appreciate some advice of more experienced people on here where I'm going wrong if possible.
I have several subs following MYBACKUP1 DOWN TO 180 ODD and want each backup to be triggered by time cells starting at cell z35 going down to Z180 but finding them very hit and miss, particularly if my sheet is resorted via times in column Z...

Private Sub Workbook_Open()  
       
    Application.OnTime TimeValue(Range("$Z$35").Text), "MYBACKUP1"    '  $Z$35  is the Trigger time I want for MYBACKUP1 to kick off
    Application.OnTime TimeValue(Range("$Z$36").Text), "MYBACKUP2"    '  $Z$36  is the Trigger time I want for MYBACKUP2 to kick off
End Sub

Sub MYBACKUP1()
'
 ' MYBACKUP1 Macro
' Macro recorded 6/9/2013 
' $Z$35 IS THE TRIGGER TIME i NEED FOR MYBACKUP1
' $Q$17 IS A CELL REFERENCED TO MY SYSTEM CLOCK AND SIMPLY HAS THE FORMULA =NOW() INSIDE IT.
' DATA1 IS THE DETAILS i NEED COPIED TO $BZ$35 WHEN MY SYSTEM CLOCK MATCHES THAT OF Z$35 

Application.ScreenUpdating = False
If Sheet1.Range("$Z$35").Value > Sheet1.Range("$Q$17").Value Then Exit Sub  
If ActiveSheet.Name <> "WhiteBoard" Then Exit Sub
If Sheet1.Range("$B$35") = "" And Sheet1.Range("$B$35") = "" Then Exit Sub
If Sheet1.Range("$BZ$35").Value <> "" Then Exit Sub
If Sheet1.Range("$Z$35").Value <= Sheet1.Range("$Q$17").Value Then

ActiveSheet.Unprotect
Range("DATA1").Offset(, 76) = Range("DATA1").Value      ' DATA1 Range copied to $BZ$35 here.
ActiveSheet.Protect
End If
Application.ScreenUpdating = True
End Sub