Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell with static current time
i would like a cells in a column to display the curent static time the cell
in the previous column is a certain value ex: if cell value is "out" or not "in" then next cell to display when value changed. any answers? macros, formulas, setting changes? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell with static current time
Does the cell change because of manual entry or does it have a formula in it?
-- Gary''s Student - gsnu200743 "ben w" wrote: i would like a cells in a column to display the curent static time the cell in the previous column is a certain value ex: if cell value is "out" or not "in" then next cell to display when value changed. any answers? macros, formulas, setting changes? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell with static current time
cell has list validation
"Gary''s Student" wrote: Does the cell change because of manual entry or does it have a formula in it? -- Gary''s Student - gsnu200743 "ben w" wrote: i would like a cells in a column to display the curent static time the cell in the previous column is a certain value ex: if cell value is "out" or not "in" then next cell to display when value changed. any answers? macros, formulas, setting changes? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell with static current time
cell with list causes insertion of current static time in another cell, when
original cell is a certain value "ben w" wrote: cell has list validation "Gary''s Student" wrote: Does the cell change because of manual entry or does it have a formula in it? -- Gary''s Student - gsnu200743 "ben w" wrote: i would like a cells in a column to display the curent static time the cell in the previous column is a certain value ex: if cell value is "out" or not "in" then next cell to display when value changed. any answers? macros, formulas, setting changes? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell with static current time
Here is an example using A1 and B1. Data goes in A1 and the time is tagged
in B1. If you insert OUT (data validation or manually) the time is statically recorded in B1. Once the time is entered, it will not update, even if somebody comes along and messes with A1. The only way to refresh B1 would be to clear it. That allows re-tagging: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If Range("A1").Value < "OUT" Then Exit Sub If Range("B1").Value < "" Then Exit Sub Application.EnableEvents = False Range("B1").Value = Time Application.EnableEvents = True End Sub Note that this is Worksheet Event code. It does NOT go in a standard module. -- Gary''s Student - gsnu200743 "ben w" wrote: cell has list validation "Gary''s Student" wrote: Does the cell change because of manual entry or does it have a formula in it? -- Gary''s Student - gsnu200743 "ben w" wrote: i would like a cells in a column to display the curent static time the cell in the previous column is a certain value ex: if cell value is "out" or not "in" then next cell to display when value changed. any answers? macros, formulas, setting changes? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell with static current time
thanks, this works, but i tried changing the range from just a1 to entire
column and out put to entire column b, but did not work, not that familiar with this, how would i change it? "Gary''s Student" wrote: Here is an example using A1 and B1. Data goes in A1 and the time is tagged in B1. If you insert OUT (data validation or manually) the time is statically recorded in B1. Once the time is entered, it will not update, even if somebody comes along and messes with A1. The only way to refresh B1 would be to clear it. That allows re-tagging: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If Range("A1").Value < "OUT" Then Exit Sub If Range("B1").Value < "" Then Exit Sub Application.EnableEvents = False Range("B1").Value = Time Application.EnableEvents = True End Sub Note that this is Worksheet Event code. It does NOT go in a standard module. -- Gary''s Student - gsnu200743 "ben w" wrote: cell has list validation "Gary''s Student" wrote: Does the cell change because of manual entry or does it have a formula in it? -- Gary''s Student - gsnu200743 "ben w" wrote: i would like a cells in a column to display the curent static time the cell in the previous column is a certain value ex: if cell value is "out" or not "in" then next cell to display when value changed. any answers? macros, formulas, setting changes? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell with static current time
I will get back to you later today.
-- Gary''s Student - gsnu200743 "ben w" wrote: thanks, this works, but i tried changing the range from just a1 to entire column and out put to entire column b, but did not work, not that familiar with this, how would i change it? "Gary''s Student" wrote: Here is an example using A1 and B1. Data goes in A1 and the time is tagged in B1. If you insert OUT (data validation or manually) the time is statically recorded in B1. Once the time is entered, it will not update, even if somebody comes along and messes with A1. The only way to refresh B1 would be to clear it. That allows re-tagging: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If Range("A1").Value < "OUT" Then Exit Sub If Range("B1").Value < "" Then Exit Sub Application.EnableEvents = False Range("B1").Value = Time Application.EnableEvents = True End Sub Note that this is Worksheet Event code. It does NOT go in a standard module. -- Gary''s Student - gsnu200743 "ben w" wrote: cell has list validation "Gary''s Student" wrote: Does the cell change because of manual entry or does it have a formula in it? -- Gary''s Student - gsnu200743 "ben w" wrote: i would like a cells in a column to display the curent static time the cell in the previous column is a certain value ex: if cell value is "out" or not "in" then next cell to display when value changed. any answers? macros, formulas, setting changes? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell with static current time
Hii ben:
Here is the new version that tags all of column A: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub If Target.Value < "OUT" Then Exit Sub If Target.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False Target.Offset(0, 1).Value = Time Application.EnableEvents = True End Sub Study it to see how we went from a single cell to a column. REMEMBER: You have to erase the old version. -- Gary''s Student - gsnu200743 "ben w" wrote: thanks, this works, but i tried changing the range from just a1 to entire column and out put to entire column b, but did not work, not that familiar with this, how would i change it? "Gary''s Student" wrote: Here is an example using A1 and B1. Data goes in A1 and the time is tagged in B1. If you insert OUT (data validation or manually) the time is statically recorded in B1. Once the time is entered, it will not update, even if somebody comes along and messes with A1. The only way to refresh B1 would be to clear it. That allows re-tagging: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If Range("A1").Value < "OUT" Then Exit Sub If Range("B1").Value < "" Then Exit Sub Application.EnableEvents = False Range("B1").Value = Time Application.EnableEvents = True End Sub Note that this is Worksheet Event code. It does NOT go in a standard module. -- Gary''s Student - gsnu200743 "ben w" wrote: cell has list validation "Gary''s Student" wrote: Does the cell change because of manual entry or does it have a formula in it? -- Gary''s Student - gsnu200743 "ben w" wrote: i would like a cells in a column to display the curent static time the cell in the previous column is a certain value ex: if cell value is "out" or not "in" then next cell to display when value changed. any answers? macros, formulas, setting changes? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell with static current time
THANKS, THIS WORKS GREAT! I APPRECIATE ALL YOUR HELP AND THE TIP.
"Gary''s Student" wrote: Hii ben: Here is the new version that tags all of column A: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub If Target.Value < "OUT" Then Exit Sub If Target.Offset(0, 1).Value < "" Then Exit Sub Application.EnableEvents = False Target.Offset(0, 1).Value = Time Application.EnableEvents = True End Sub Study it to see how we went from a single cell to a column. REMEMBER: You have to erase the old version. -- Gary''s Student - gsnu200743 "ben w" wrote: thanks, this works, but i tried changing the range from just a1 to entire column and out put to entire column b, but did not work, not that familiar with this, how would i change it? "Gary''s Student" wrote: Here is an example using A1 and B1. Data goes in A1 and the time is tagged in B1. If you insert OUT (data validation or manually) the time is statically recorded in B1. Once the time is entered, it will not update, even if somebody comes along and messes with A1. The only way to refresh B1 would be to clear it. That allows re-tagging: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub If Range("A1").Value < "OUT" Then Exit Sub If Range("B1").Value < "" Then Exit Sub Application.EnableEvents = False Range("B1").Value = Time Application.EnableEvents = True End Sub Note that this is Worksheet Event code. It does NOT go in a standard module. -- Gary''s Student - gsnu200743 "ben w" wrote: cell has list validation "Gary''s Student" wrote: Does the cell change because of manual entry or does it have a formula in it? -- Gary''s Student - gsnu200743 "ben w" wrote: i would like a cells in a column to display the curent static time the cell in the previous column is a certain value ex: if cell value is "out" or not "in" then next cell to display when value changed. any answers? macros, formulas, setting changes? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: How to insert current time (static) to the nearest second? | Excel Worksheet Functions | |||
How to add todays date (static) to the current active cell using m | Excel Discussion (Misc queries) | |||
how do I insert the current time (static) and date in a cell? | Excel Discussion (Misc queries) | |||
How to enter current static time in Excel in 00:00:00.0 format? | Excel Worksheet Functions | |||
Excel static current date/time problem | Excel Worksheet Functions |