Live Auto-Clock and Popup Messages

[drefiek]

Good Morning,

I would appreciate help with the following as I've been stuck on it for days and can't seem to work it out. Google hasn't helped much either!

I've managed to set up a live auto-clock which displays live time as per my system time, to the second, using the following code in Sheet1 and a separate module. It works great.



Dim SchedRecalc As Date


Sub Recalc()


With Sheet1.Range("A1")


.Value = Format(Time, "hh:mm:ss")


End With


Call SetTime


End Sub


Sub SetTime()


SchedRecalc = Now + TimeValue("00:00:01")


Application.OnTime SchedRecalc, "Recalc"


End Sub


Sub Disable()


On Error Resume Next


Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False


End Sub



Now what I want is for popup message boxes (requiring just an 'OK' click) to appear when that live clocks reaches certain times. I've tried everything I am aware of like the following but it just won't work. When I run the macro on it's own it works, it's just when I try to link it to the other codes it won't work.

Private Sub Popup1()
If cell(A1) = "08:55:00" Then
Run "Popup"
End Sub

Any ideas please?

 

[Andrew Poulsom]

Maybe try:

Sub Recalc()
    With Sheet1.Range("A1")
        .Value = Format(Time, "hh:mm:ss")
        If .Value = "08:55:00" Then
            MsgBox "It's five to nine"
        End If
    End With
    Call SetTime
End Sub

 

[drefiek]

Hi Andrew,

Thanks for the speedy response!

However the additional code you suggested doesn't work. I set it up for a few minutes ahead and waited but no message popped up

Regards,
Keifer

 

[Stiuart_W]

It's to do with pesky formats.


Try this


If Format(.Value, "hh:mm:ss") = "10:07:00" Then



I tried and it worked for me

 

[drefiek]

Hi Stiuart_W,

Yes it worked a treat, thanks very much!

 

[Andrew Poulsom]

I guess you could also use:

If .Text = "08:55:00" Then

 

[drefiek]

Hi all,

I have a final little request for help.

Now I have the auto live clock in and working with popups at certain times, I now want to apply a certain message popup to a cell that ONLY requires filling in on a Tuesday at 9am. The cell is E36. So I want the popup only to be displayed on a Tuesday at 9am. I think to make this work Excel would need to know that today is a Tuesday, maybe by referring to the system date and time (as it does for the live clock).

Any help would be greatly appreciated again.

Many thanks,
Keifer

 

[Stiuart_W]

Hi Keifer


I don't understand what you mean by this "...certain message popup to a cell that ONLY requires filling in on a Tuesday at 9am. The cell is E36......".


You mean you would like the message to say "Oi, you, yes you!! Fill in Cell E36 now and I mean NOW!!!"?

 

[drefiek]

Hi Stiuart_W,

Sorry my fault, I was rushing and didn't word it very well!

Basically there is a cell (E36) which should only be filled in on a Tuesday at 9am. If I set up the popup message as per the above, to popup at 9am, it will show up every day at 9am... so I need an additional code to make it so that it only popups up on a Tuesday.

Hopefully that makes a bit more sense!

Regards,
Keifer

 

[Stiuart_W]

Try this Kiefer

Sub Recalc()
    DayToday = WeekdayName(Weekday(Now()), , vbSunday)
    With Sheet1.Range("A1")
        .Value = Format(Time, "hh:mm:ss")
            If Format(.Value, "hh:mm:ss") = "15:32:00" And DayToday = "Tuesday" Then
                MsgBox "Fill in E36 you gimp"
            End If
    End With
    Call SetTime
End Sub