RE: Expressing Application.onTime("00:00:10") as a variable...

[chazrab]

RE: Expressing Application.onTime("00:00:10") as a variable...

I just want to be able to vary the time to display a MsgBox using a variable
from a cell value on a sheet using Application.onTime("h:m:s"), where h, m and t are variables In the title, a variable evaluates to 10 seconds. Would anyone know how to do that?
Thx for anyone's help
cr

 

[JLGWhiz]

Re: Expressing Application.onTime("00:00:10") as a variable...

You can use a varible for the time like this.

Sub t()
[COLOR=#ff0000]Dim tm As Date
tm = Range("B2").Value
[/COLOR]Application.OnTime ([COLOR=#ff0000]tm[/COLOR]), "tt"
End Sub


Sub tt()
MsgBox "It Worked"
End Sub

The value in B2 would be entered in 00:00:00 format.

 

[chazrab]

Re: Expressing Application.onTime("00:00:10") as a variable...

You can use a varible for the time like this.

Sub t()
[COLOR=#ff0000]Dim tm As Date
tm = Range("B2").Value
[/COLOR]Application.OnTime ([COLOR=#ff0000]tm[/COLOR]), "tt"
End Sub


Sub tt()
MsgBox "It Worked"
End Sub

The value in B2 would be entered in 00:00:00 format.

I'm putting, 1, 2 3, etc. for hours in cell B2. Nothing happens. I just want to have the MsgBox appear whenever I want based on the value in B2. What am I doing wrong ? The cell is formated as number with no decimal places. Thx for your help. cr

 

[JLGWhiz]

Re: Expressing Application.onTime("00:00:10") as a variable...

Apparently you did not read the last line in post #2 . The value in cell B2 must be formatted as time, eg: 00:00:00. So if you want 1 hour from current time then

Time + 01:00:00

would go in cell B2, If you want a specific time like 9:45 PM then cell B2 would be

21:45:00

The tm variable must equate to the "00:00:00" format for the code to work.