Custom Format Time Code

[Texas Aggie]

Here's my code:

Dim time As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set time = ws.Range(B10)

If time.Value = Format(Range("TimeLeft").Value, "00:00:10:00") Then
MsgBox ("Prepare to begin Meeting")
End If

What I am trying to do is display a message when the clock reached 10
minutes or 00:00:10:00. The format of the cell is dd:hh:mm:ss;@. Thanks in
advance.

 

[Bob Phillips]

If time.Value = TimeSertial(0,10,0) Then
MsgBox ("Prepare to begin Meeting")
End If


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Texas Aggie]

No work.

Here is the whole module code. Maybe there is something else influencing it.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1 ' one second
Public Const cRunWhat = "DateTimeStamp"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=True
End Sub

Sub DateTimeStamp()
Dim ws As Worksheet
Set ws = Worksheets("Welcome")
ws.Range("Date").Value = Format(Now, "dddd mmm dd, yyyy")
ws.Range("L4").Value = Format(Now, "mm/dd/yyyy")
ws.Range("Time").Value = Format(time, "hh:mm:ss AM/PM")
StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False
End Sub

Sub WarningMsg()

Dim time As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set time = ws.Range(B10)

If time.Value = Format(Range("TimeLeft").Value, "03:13:15:30") Then
MsgBox ("Prepare to begin Meeting")
End If

If time.Value = Format(Range("TimeLeft").Value, "03:13:15:20") Then
MsgBox ("Begin Meeting")
End If
End Sub

--

Fighting Texas Aggie Class of 2009

If time.Value = TimeSertial(0,10,0) Then
MsgBox ("Prepare to begin Meeting")
End If


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

I can't figure out what you're trying to do with Format...

For instance, "03:13:15:30" is certainly not a valid time format.

In general, you don't need to worry about format at all when you're
dealing with the cell .Value property. Since XL stores values as
fractional days, you could just check if your "TimeLeft" range contains
a time value of 10 minutes or less:

Public Sub WarningMsg()
Static bAlreadyWarned
With ActiveSheet.Range("TimeLeft")
If .Value <= 0 Then
MsgBox "Begin Meeting"
ElseIf .Value <= TimeSerial(0, 10, 0) Then
If Not bAlreadyWarned Then
MsgBox "Prepare for Meeting"
bAlreadyWarned = True
End If
End If
End With
End Sub

No work.

Here is the whole module code. Maybe there is something else influencing it.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1 ' one second
Public Const cRunWhat = "DateTimeStamp"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=True
End Sub

Sub DateTimeStamp()
Dim ws As Worksheet
Set ws = Worksheets("Welcome")
ws.Range("Date").Value = Format(Now, "dddd mmm dd, yyyy")
ws.Range("L4").Value = Format(Now, "mm/dd/yyyy")
ws.Range("Time").Value = Format(time, "hh:mm:ss AM/PM")
StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False
End Sub

Sub WarningMsg()

Dim time As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set time = ws.Range(B10)

If time.Value = Format(Range("TimeLeft").Value, "03:13:15:30") Then
MsgBox ("Prepare to begin Meeting")
End If

If time.Value = Format(Range("TimeLeft").Value, "03:13:15:20") Then
MsgBox ("Begin Meeting")
End If
End Sub