Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stamp a Cell with Time Q
I have listed in Sheet5 A1:A7, days i.e. Monday (A1) through Sunday
(A7). How would I via code, stamp in Sheet5 B1:B7 the current time when an action is performed, depending on the Day of the week - 1 (minus one)? For example, now is Sunday at 19:58, thus when I perfom an action I wish B6 (Sat) to be poplulated with 19:58, tomorrow if I perform the same action, say at 10:07, I want B7 (Sun) to be populated. (the info I'm reporting on is always a day before the actual day) The only 'quirk' I want is that if the time is after 21:00 on the day I want the appropriate cell in B1:B7 left blank Hope the above is not too confusing Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stamp a Cell with Time Q
A worksheet change function like the one below should work. Format sheet 5
column B for time (any time format you prefer). I placed the code below on sheet 1 in the VBA project window. Worksheet change must be placed in every worksheet you want the action to take place. If you place the code on sheet 5 then you have to add an if statement to ignore range A1:B7. Sub worksheet_change(ByVal Target As Range) Myrow = Weekday(Now() - 1, vbMonday) If Hour(Now()) < 21 Then Sheets("Sheet5").Cells(Myrow, "B") = _ Now() End If End Sub "Sean" wrote: I have listed in Sheet5 A1:A7, days i.e. Monday (A1) through Sunday (A7). How would I via code, stamp in Sheet5 B1:B7 the current time when an action is performed, depending on the Day of the week - 1 (minus one)? For example, now is Sunday at 19:58, thus when I perfom an action I wish B6 (Sat) to be poplulated with 19:58, tomorrow if I perform the same action, say at 10:07, I want B7 (Sun) to be populated. (the info I'm reporting on is always a day before the actual day) The only 'quirk' I want is that if the time is after 21:00 on the day I want the appropriate cell in B1:B7 left blank Hope the above is not too confusing Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stamp a Cell with Time Q
Put the following code in a standard code module, then call it from a
button on a toolbar or worksheet. It formats the cell's number format automatically as each value is entered, or you could simply format the cells in column B ahead of time. '---------------------------------------------------------------------- Public Sub StampTime() Dim dblNow As Double Dim dblTimeNow As Double Dim lngDayOfWeek As Long Dim lngStampRow As Long Dim rngStampCell As Range dblNow = Now With WorksheetFunction dblTimeNow = dblNow - .RoundDown(dblNow, 0) lngDayOfWeek = .Weekday(.RoundDown(dblNow, 0)) End With lngStampRow = ((lngDayOfWeek + 4) Mod 7) + 1 Set rngStampCell = Sheet5.Cells(lngStampRow, 2) With rngStampCell If (dblTimeNow (21# / 24#)) _ Then .ClearContents Else .Value = dblNow .NumberFormat = "hh:mm" End If End With End Sub -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stamp a Cell with Time Q
Bill: How does your code automaticaly update sheet 5 times. Your code only
works if somebody manually runs the macro. Weekday is a VBA function and doesn't have to be called as a worksheetfunction. "Bill Renaud" wrote: Put the following code in a standard code module, then call it from a button on a toolbar or worksheet. It formats the cell's number format automatically as each value is entered, or you could simply format the cells in column B ahead of time. '---------------------------------------------------------------------- Public Sub StampTime() Dim dblNow As Double Dim dblTimeNow As Double Dim lngDayOfWeek As Long Dim lngStampRow As Long Dim rngStampCell As Range dblNow = Now With WorksheetFunction dblTimeNow = dblNow - .RoundDown(dblNow, 0) lngDayOfWeek = .Weekday(.RoundDown(dblNow, 0)) End With lngStampRow = ((lngDayOfWeek + 4) Mod 7) + 1 Set rngStampCell = Sheet5.Cells(lngStampRow, 2) With rngStampCell If (dblTimeNow (21# / 24#)) _ Then .ClearContents Else .Value = dblNow .NumberFormat = "hh:mm" End If End With End Sub -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stamp a Cell with Time Q
I guess it wasn't clear to me that the OP wanted these cells
automatically updated every time somebody entered something in ANY cell, so I left it this way to provide more flexibility. If it needs to be called anytime any cell is changed on this same worksheet (or any other worksheet, for that matter), then put this code in the Worksheet_Change event handler for this (or any other) worksheet (it simply calls the StampTime routine): '---------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) StampTime End Sub As a matter of programming flexibility, this is a better way to do event routines anyway. I can print out the standard code module, and have virtually all of the code, without having to go check each worksheet and chart code module. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is it possible to time stamp cell comments? | Excel Discussion (Misc queries) | |||
is it possible to time stamp cell comments? | Excel Discussion (Misc queries) | |||
time stamp a cell that doesn,t change when time stamping another | Excel Programming | |||
Inserting time stamp onchange of any cell | Excel Programming | |||
Setting up a date and time stamp in a cell | Excel Programming |