Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter timestamp when column is modified
I wrote following code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 And Target.Value = 100 Then Cells(Target.Row, 3).Value = Date & " - " & Time Target.Value = Target.Value / 100 Target.Style = "Percent" End If End Sub The idea of this is when i enter value 100 in column B, then timestamp is inserted in column C. I know this code sux - last 2 rows of this code were written cuz i didn't know how to triger this macro by entering value - 100%. I want to know: 1) how do i triger a macro when value 100% is entered? I mean 100 is entered in cell which is formated as % ? 2) how do i format my timestamp so it enters date & time and then i can add rest of the formating through cell formating ? For example when i enter date and time by pressing ctrl + ; then space and then ctrl + shift + ; i get "date time" and after this i can format this datetime as i want. But after i get the same through macro excel does not respond to formating. 3) i want to add last modified timestamp in column 5 which would trigger each time value is entered/changed in column 2 how do i do this ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter timestamp when column is modified
First, 100% = 1. So you only need to check to see if the value is 1.
But entering percentages in excel can be maddening. There's an option under tools|options|edit tab that can make life easier (or worse--depending if you like it or not). From xl2003's help: Enable automatic percent entry Select to multiply by 100 all numbers less than 1 that you enter in cells formatted in the Percentage format. Clear this check box to multiply by 100 all numbers that you enter in cells formatted in the Percentage format, including numbers equal to or greater than 1. (I'd format that whole column as a percentage first.) And your code showed column 3 (C) instead of column 5 (E) in your text. I used E. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Cells.Count 1 Then Exit Sub 'one cell at a time If Intersect(.Cells, Me.Range("b:b")) Is Nothing Then Exit Sub On Error GoTo errHandler: If IsNumeric(.Value) Then '.Style = "Percent" If .Value = 1 Then Application.EnableEvents = False With .Offset(0, 3) .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With End If End If End With errHandler: Application.EnableEvents = True End Sub You can uncomment the .style command if you didn't format it as percentage. The .enableevents stuff stops the code from getting called again when you add the date/time. Mikus wrote: I wrote following code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 And Target.Value = 100 Then Cells(Target.Row, 3).Value = Date & " - " & Time Target.Value = Target.Value / 100 Target.Style = "Percent" End If End Sub The idea of this is when i enter value 100 in column B, then timestamp is inserted in column C. I know this code sux - last 2 rows of this code were written cuz i didn't know how to triger this macro by entering value - 100%. I want to know: 1) how do i triger a macro when value 100% is entered? I mean 100 is entered in cell which is formated as % ? 2) how do i format my timestamp so it enters date & time and then i can add rest of the formating through cell formating ? For example when i enter date and time by pressing ctrl + ; then space and then ctrl + shift + ; i get "date time" and after this i can format this datetime as i want. But after i get the same through macro excel does not respond to formating. 3) i want to add last modified timestamp in column 5 which would trigger each time value is entered/changed in column 2 how do i do this ? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter timestamp when column is modified
How do i modify this code to enter timestamp in column L if column B contain
ANY value "Dave Peterson" wrote: First, 100% = 1. So you only need to check to see if the value is 1. But entering percentages in excel can be maddening. There's an option under tools|options|edit tab that can make life easier (or worse--depending if you like it or not). From xl2003's help: Enable automatic percent entry Select to multiply by 100 all numbers less than 1 that you enter in cells formatted in the Percentage format. Clear this check box to multiply by 100 all numbers that you enter in cells formatted in the Percentage format, including numbers equal to or greater than 1. (I'd format that whole column as a percentage first.) And your code showed column 3 (C) instead of column 5 (E) in your text. I used E. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Cells.Count 1 Then Exit Sub 'one cell at a time If Intersect(.Cells, Me.Range("b:b")) Is Nothing Then Exit Sub On Error GoTo errHandler: If IsNumeric(.Value) Then '.Style = "Percent" If .Value = 1 Then Application.EnableEvents = False With .Offset(0, 3) .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With End If End If End With errHandler: Application.EnableEvents = True End Sub You can uncomment the .style command if you didn't format it as percentage. The .enableevents stuff stops the code from getting called again when you add the date/time. Mikus wrote: I wrote following code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 And Target.Value = 100 Then Cells(Target.Row, 3).Value = Date & " - " & Time Target.Value = Target.Value / 100 Target.Style = "Percent" End If End Sub The idea of this is when i enter value 100 in column B, then timestamp is inserted in column C. I know this code sux - last 2 rows of this code were written cuz i didn't know how to triger this macro by entering value - 100%. I want to know: 1) how do i triger a macro when value 100% is entered? I mean 100 is entered in cell which is formated as % ? 2) how do i format my timestamp so it enters date & time and then i can add rest of the formating through cell formating ? For example when i enter date and time by pressing ctrl + ; then space and then ctrl + shift + ; i get "date time" and after this i can format this datetime as i want. But after i get the same through macro excel does not respond to formating. 3) i want to add last modified timestamp in column 5 which would trigger each time value is entered/changed in column 2 how do i do this ? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter timestamp when column is modified
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter timestamp when column is modified
Just to add to Don's post...
You could drop the isnumeric() check. And the offset becomes 10. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Cells.Count 1 Then Exit Sub 'one cell at a time If Intersect(.Cells, Me.Range("b:b")) Is Nothing Then Exit Sub On Error GoTo errHandler: If .Value < "" Then Application.EnableEvents = False With .Offset(0, 10) .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With End If End With errHandler: Application.EnableEvents = True End Sub Mikus wrote: How do i modify this code to enter timestamp in column L if column B contain ANY value "Dave Peterson" wrote: First, 100% = 1. So you only need to check to see if the value is 1. But entering percentages in excel can be maddening. There's an option under tools|options|edit tab that can make life easier (or worse--depending if you like it or not). From xl2003's help: Enable automatic percent entry Select to multiply by 100 all numbers less than 1 that you enter in cells formatted in the Percentage format. Clear this check box to multiply by 100 all numbers that you enter in cells formatted in the Percentage format, including numbers equal to or greater than 1. (I'd format that whole column as a percentage first.) And your code showed column 3 (C) instead of column 5 (E) in your text. I used E. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Cells.Count 1 Then Exit Sub 'one cell at a time If Intersect(.Cells, Me.Range("b:b")) Is Nothing Then Exit Sub On Error GoTo errHandler: If IsNumeric(.Value) Then '.Style = "Percent" If .Value = 1 Then Application.EnableEvents = False With .Offset(0, 3) .Value = Now .NumberFormat = "mm/dd/yyyy hh:mm:ss" End With End If End If End With errHandler: Application.EnableEvents = True End Sub You can uncomment the .style command if you didn't format it as percentage. The .enableevents stuff stops the code from getting called again when you add the date/time. Mikus wrote: I wrote following code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 And Target.Value = 100 Then Cells(Target.Row, 3).Value = Date & " - " & Time Target.Value = Target.Value / 100 Target.Style = "Percent" End If End Sub The idea of this is when i enter value 100 in column B, then timestamp is inserted in column C. I know this code sux - last 2 rows of this code were written cuz i didn't know how to triger this macro by entering value - 100%. I want to know: 1) how do i triger a macro when value 100% is entered? I mean 100 is entered in cell which is formated as % ? 2) how do i format my timestamp so it enters date & time and then i can add rest of the formating through cell formating ? For example when i enter date and time by pressing ctrl + ; then space and then ctrl + shift + ; i get "date time" and after this i can format this datetime as i want. But after i get the same through macro excel does not respond to formating. 3) i want to add last modified timestamp in column 5 which would trigger each time value is entered/changed in column 2 how do i do this ? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"last modified" timestamp function in excel 2003 | New Users to Excel | |||
dynamically enter name of last modified by in spreadsheet on save | Excel Discussion (Misc queries) | |||
Determine if cell was modified by Enter , Paste or Delete | Excel Programming | |||
How do I enter the date last modified in the footer of an Excel d. | Excel Discussion (Misc queries) | |||
Filename and creation timestamp in 2-column combobox | Excel Programming |