Excel: Countdown Timer/Alarm [Not Rocket Science, still useful] 1

[xlhelp]

I work for a company where any external software (other than approved) is stictly forbidden. So, I have to forget about downloading anything for my system at work.

I am one of those people that will say "I'll call you back in 10 mins" or "I'll come come and see you in 15", etc. Then, two hours later, panic; because I got involved with other things.

So, I had to devise something on my own. This, as I said in the subject line, is not rocket sicence, but still a useful tool for me.

I have set up two very short macros in a file by itself.

Sub SetAlarm()

tmm = Application.InputBox(Prompt:="How long do you want to set the alarm for(Mins)?", Title:="Set Alarm:", Type:=1)

If tmm < 9 Then
tmm = "0" & tmm
End If

timmn = "00:" & tmm & ":00"

If tmm > 59 Then
dif = tmm - 60 * Int(tmm / 60)
If dif < 10 Then
dif = "0" & dif
End If
timmn = "0" & Int(tmm / 60) & ":" & dif & ":00"
End If


Application.OnTime Now + TimeValue(timmn), "DisplayAlarm"

End Sub


Sub DisplayAlarm()

Beep
ForceForegroundWindow FindWindowA(0, Application.Caption)

Range("G15").Comment.Visible = True

End Sub

In cell G15's comment box I have embedded a picture (Doutzen Kroes) that pops up no matter what application I am in when the alarm goes off.

Then, I just right click on the cell and select "Hide Comment". I could create a Worksheet_SelectionChange event to hide the comment by setting the Visible property to False.

Instead of Range("G15").Comment.Visible = True above, a message box can be used
MsgBox "Wake up. Its time for your break!" If message box is used, the macros can be put in a Personal.xls file.

I have the setalarm macro on a short-cut key, so it takes only few seconds to set the alarm.

PS: Never figured out why Rocket Science and Brain Surgery rank high up there. I think Excel-ers are just as good.



Member- AAAA Association Against Acronym Abusers

 

[BaldyMcFatFat]

xlhelp,

This was extremely useful, thank you. Only one question. I'm the type of person that is usually looking through eighty things at once and sometimes get a little over excited when closing certain windows that I'll accidently close something I meant to stay open. With that in mind, is there a way to set it so that even if the workbook you have this in gets closed, the alarm will still go off? Or does the workbook always need to be open? Thank you in advance for any information you might be able to provide.

 

[GlennUK]

Hi BaldyMcFatFat,

if you look towards the end of the post it says that you can use a message box and put the macros in the Personal.xls. That way the worksheet window will be hidden ( and therefore not easy to accidentally close ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[BaldyMcFatFat]

Thanks Glenn

"If at first you don't succeed, skydiving is not for you.

 

[Madawc]

You can also do it in Outlook, though not easily. Set a 'meeting' with just yourself and included a timed reminded.

Tasks could also be used.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[BaldyMcFatFat]

I have outlook on my main desk top but can't access it from some of the other computers I may have to work from. The alram using Excel works great for me causing I just have it on a thumb drive that can plug into any computer and access. Thank you to both of you.

Baldy McFatfat

"If at first you don't succeed, skydiving is not for you.

 

[Bandenjamin]

This rules. I set up something to use for poker events using this and wow. I love it. One line I had to add in case I accidentially launched the macro was

If tmm = 0 then
MsgBox("idiot!")
End If

Obviously I pick on myself a little

--Dan
Whenever you find yourself on the side of the majority, it is time to pause and reflect.
Mark Twain

 

[Gavona]

Hey folks, how about awarding a star if this was so useful to you both?

Gavin