MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Time Alarm

  Asked By: Lucina    Date: Dec 23    Category: MS Office    Views: 1789

If you can know less than newbie, that would be me. What I'm
trying to do is log employees in on spreadsheet. Then have additional
cells that are 2, 4 and 6 hours later so we can contact those
employees for safety reasons. I work for an electric utility and this
will be used during major storms. I've, with a lot of help, figured
out how to use formulas and get the extended times I need, but I can't
figure out how to set an alarm on those cells. Maybe turn red. Is
there a formula that will do that? I have access to Microsoft Visual
Studio.net, but don't know anyone who knows how to use it. All help
would really be appreciated.



1 Answer Found

Answer #1    Answered By: Rosie Hughes     Answered On: Dec 23

There isn’t a built-in formula  function in Excel that will let you set  an alarm
on a cell. Here’s the best approach I have come up with since reading your

1. Add a VBA module to your workbook. Paste the following custom
function into it:

Public Function CheckTime() As Boolean
If Application.Caller.Value <= Now() Then
CheckTime = True
CheckTime = False
End If
End Function

2. Paste the following event code in the code window for ThisWorkbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Calc whenever any sheet in this workbook is activated
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
'Calc whenever a different cell is selected in this workbook
End Sub

3. Back in regular Excel, select one cell on the worksheet with a time
in it (for example, B9). Select Conditional Formatting from the Format menu.
The Conditional Formatting dialog appears. Select ‘Formula is” and enter the
following formula:


Click the Format button. On the Patterns tab, click the color you want, then
OK. Click OK to end the Conditional Formatting dialog. Use Format Painter to
copy this cell’s formatting to every cell (or whole columns) where the times
will be stored.

Every time  the workbook is recalculated, the conditional formatting will call
CheckTime (for each cell with that formatting). If the time in a cell is
earlier or the same as Now(), CheckTime will return True, and the cell will be
colored red  (or whatever color you chose).

The workbook will recalculate whenever the workbook is activated, whenever a
different cell is selected in the workbook, or when you press F9. I set it up
this way because the OnTime event in Excel VBA is not reliable – it is too
easily disrupted.

I used the Now() function in my test workbook, so my cells  have both date &
time. If you have times only (no dates), the CheckTime function would have to be
modified. It would be easy to put a time-stamp button on your worksheet, which
would enter the current & future times as values. If you want help  with this, or
have trouble testing the other suggestions, let me know and I will try to help.

Didn't find what you were looking for? Find more on Time Alarm Or get search suggestion and latest updates.