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 ?

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

try

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 2 And Target = 100 Then
Target = Format(Target / 100, "0.00%")
Cells(Target.Row, 3).Value = Date & " - " & Time
End If
Application.EnableEvents = True
End Sub
--
Don Guillett
SalesAid Software
[email protected]
"Mikus" <[email protected]> wrote in message
news:[email protected]...
> 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 ?

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
>

try

if target<>""

--
Don Guillett
SalesAid Software
[email protected]
"Mikus" <[email protected]> wrote in message
news:[email protected]...
> 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
> >

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