Hi,

I tried to figure out how to make the code that JE McGimpsey has posted on
his site: http://www.mcgimpsey.com/excel/timestamp.html "DateStampMacro" but
just do not have the VBA skills to decipher how I need to change it to work
for me.

I'm trying to have a date time stamp in cell "D1" whenever my user changes
data in either of two ranges "C18:V32" and "C37:D43".

This is a customer tool for pricing and I want to record the date that they
changed inputs that affect price.

This is xl2003

These ranges have all kinds of data validation rules that are generated from
sheets that are protected and this workbook and worksheet will also be
protected to keep the user from overrighting formulas etc.

Thanks in advance!

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


You may want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
me.unprotect password:="Hi"
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
me.protect password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub

If d1 is locked on that protected sheet.


Steve E wrote:

Hi,

I tried to figure out how to make the code that JE McGimpsey has posted on
his site: http://www.mcgimpsey.com/excel/timestamp.html "DateStampMacro" but
just do not have the VBA skills to decipher how I need to change it to work
for me.

I'm trying to have a date time stamp in cell "D1" whenever my user changes
data in either of two ranges "C18:V32" and "C37:D43".

This is a customer tool for pricing and I want to record the date that they
changed inputs that affect price.

This is xl2003

These ranges have all kinds of data validation rules that are generated from
sheets that are protected and this workbook and worksheet will also be
protected to keep the user from overrighting formulas etc.

Thanks in advance!

--

Dave Peterson

Dave,

This is great!

Only problem is that I forgot about another Worksheet_Change event that I
have in this module:

'Private Sub Worksheet_Change(ByVal Target As Range)
' Dim Rchange As Integer
' Rchange = Target.Row ' row number selected
' If Rchange 17 And Rchange < 35 Then ' make sure only applies to
rows 18 to 34
' If Target.Address = "$D" & "$" & Rchange Then
' ' MsgBox "Target address changed : " & Target.Address
' Range("L" & Rchange, "V" & Rchange).ClearContents
' ' clears cells for in this row for cols L to T
' ' MsgBox "Range: " & " L" & Rchange & " to " & " T" & Rchange
& " Cleared" ' optional
' End If
' Else
' End If
'End Sub

when I leave it "in" I get an error "ambiguous name detected :
Worksheet_Change

??

Thanks in advance.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


You may want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
me.unprotect password:="Hi"
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
me.protect password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub

If d1 is locked on that protected sheet.


Steve E wrote:

Hi,

I tried to figure out how to make the code that JE McGimpsey has posted on
his site: http://www.mcgimpsey.com/excel/timestamp.html "DateStampMacro" but
just do not have the VBA skills to decipher how I need to change it to work
for me.

I'm trying to have a date time stamp in cell "D1" whenever my user changes
data in either of two ranges "C18:V32" and "C37:D43".

This is a customer tool for pricing and I want to record the date that they
changed inputs that affect price.

This is xl2003

These ranges have all kinds of data validation rules that are generated from
sheets that are protected and this workbook and worksheet will also be
protected to keep the user from overrighting formulas etc.

Thanks in advance!

--

Dave Peterson

Yep. Each procedure has to have its own name in any module. And since these
are defined by excel (as event procedures), you can't just rename one and have
it work.

But you can combine them into one.

It looks like the first procedure just checks for changes in D18:D34. If the
change is made there, it clears the contents of L:V of the same row.

This is the way I'd write that procedu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("D18:d34")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Me.Cells(Target.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True

End Sub

The .resize(1,11) stuff means to take the original range (L samerow as change)
and resize it to 1 row by 11 columns (L to V).

(This isn't the end product--just an aside.)


I think this may do both of the things you want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
If Not Intersect(Me.Range("D18:d34"), .Cells) Is Nothing Then
Application.EnableEvents = False
Me.Cells(.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True
End If
End With
End Sub

But I am confused about the differences in ranges.

You check rows 18:32 and 37:43 in one section and 18:34 in the other.

Seems kind of weird, but maybe that's what fits your data????

Steve E wrote:

Dave,

This is great!

Only problem is that I forgot about another Worksheet_Change event that I
have in this module:

'Private Sub Worksheet_Change(ByVal Target As Range)
' Dim Rchange As Integer
' Rchange = Target.Row ' row number selected
' If Rchange 17 And Rchange < 35 Then ' make sure only applies to
rows 18 to 34
' If Target.Address = "$D" & "$" & Rchange Then
' ' MsgBox "Target address changed : " & Target.Address
' Range("L" & Rchange, "V" & Rchange).ClearContents
' ' clears cells for in this row for cols L to T
' ' MsgBox "Range: " & " L" & Rchange & " to " & " T" & Rchange
& " Cleared" ' optional
' End If
' Else
' End If
'End Sub

when I leave it "in" I get an error "ambiguous name detected :
Worksheet_Change

??

Thanks in advance.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


You may want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
me.unprotect password:="Hi"
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
me.protect password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub

If d1 is locked on that protected sheet.


Steve E wrote:

Hi,

I tried to figure out how to make the code that JE McGimpsey has posted on
his site: http://www.mcgimpsey.com/excel/timestamp.html "DateStampMacro" but
just do not have the VBA skills to decipher how I need to change it to work
for me.

I'm trying to have a date time stamp in cell "D1" whenever my user changes
data in either of two ranges "C18:V32" and "C37:D43".

This is a customer tool for pricing and I want to record the date that they
changed inputs that affect price.

This is xl2003

These ranges have all kinds of data validation rules that are generated from
sheets that are protected and this workbook and worksheet will also be
protected to keep the user from overrighting formulas etc.

Thanks in advance!

--

Dave Peterson


--

Dave Peterson

Sheesh. How long did it take for you to learn this stuff...

Anyway... you da man.

The D18:D34 range was wrong... should have been D18:D32 this is a range
where a user enters a product model and the adjacent cells (L:V) are
variables entered dependent on the specific model selected (and therefore
entered) in the D cell. All of the inputs for L:V are dyanmic lists based on
the input to the left...

Now I have one more stupid question.

I have a form button on the top of this worksheet to allow a user to clear
out all of the information that they've entered (to make double sure that
there aren't any residual inputs that might screw them up down the road.

I recorded a macro to clear all of the necessary ranges and it worked fine
until I protect the sheet... then the macro prompts my user for a password...
which kind of defeats some of the reason that I have the sheet protected in
the first place [realizing that someone who prowls this usergroup already
knows how to crack thru the password sheet protection]... how can I convert
my macro to VBA and assign it to a command button?

Sub UnprotectClearFormProtect()
'
' UnprotectClearFormProtect Macro
' Macro recorded 9/1/2006 by Steven W. Edwards
'

'
ActiveSheet.Unprotect
Range("D1:D2,D9:G9,D12:G12,J1:L9,C18:D32,G18:V32,C 37:G43").Select
Range("C37:D42").Activate
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Thanks again for all of your help. You (and the rest of the folks that make
this user group go) rock!

Steve

"Dave Peterson" wrote:

Yep. Each procedure has to have its own name in any module. And since these
are defined by excel (as event procedures), you can't just rename one and have
it work.

But you can combine them into one.

It looks like the first procedure just checks for changes in D18:D34. If the
change is made there, it clears the contents of L:V of the same row.

This is the way I'd write that procedu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("D18:d34")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Me.Cells(Target.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True

End Sub

The .resize(1,11) stuff means to take the original range (L samerow as change)
and resize it to 1 row by 11 columns (L to V).

(This isn't the end product--just an aside.)


I think this may do both of the things you want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
If Not Intersect(Me.Range("D18:d34"), .Cells) Is Nothing Then
Application.EnableEvents = False
Me.Cells(.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True
End If
End With
End Sub

But I am confused about the differences in ranges.

You check rows 18:32 and 37:43 in one section and 18:34 in the other.

Seems kind of weird, but maybe that's what fits your data????

Steve E wrote:

Dave,

This is great!

Only problem is that I forgot about another Worksheet_Change event that I
have in this module:

'Private Sub Worksheet_Change(ByVal Target As Range)
' Dim Rchange As Integer
' Rchange = Target.Row ' row number selected
' If Rchange 17 And Rchange < 35 Then ' make sure only applies to
rows 18 to 34
' If Target.Address = "$D" & "$" & Rchange Then
' ' MsgBox "Target address changed : " & Target.Address
' Range("L" & Rchange, "V" & Rchange).ClearContents
' ' clears cells for in this row for cols L to T
' ' MsgBox "Range: " & " L" & Rchange & " to " & " T" & Rchange
& " Cleared" ' optional
' End If
' Else
' End If
'End Sub

when I leave it "in" I get an error "ambiguous name detected :
Worksheet_Change

??

Thanks in advance.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


You may want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
me.unprotect password:="Hi"
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
me.protect password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub

If d1 is locked on that protected sheet.


Steve E wrote:

Hi,

I tried to figure out how to make the code that JE McGimpsey has posted on
his site: http://www.mcgimpsey.com/excel/timestamp.html "DateStampMacro" but
just do not have the VBA skills to decipher how I need to change it to work
for me.

I'm trying to have a date time stamp in cell "D1" whenever my user changes
data in either of two ranges "C18:V32" and "C37:D43".

This is a customer tool for pricing and I want to record the date that they
changed inputs that affect price.

This is xl2003

These ranges have all kinds of data validation rules that are generated from
sheets that are protected and this workbook and worksheet will also be
protected to keep the user from overrighting formulas etc.

Thanks in advance!

--

Dave Peterson


--

Dave Peterson

Change your code to include the password.

ActiveSheet.Unprotect password:="hi"
'do the work
ActiveSheet.Protect password:="hi", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

====
And remember to protect the project from snoops.

Inside the VBE
tools|VBA Project Properties
Protection tab

(give it a memorable password).

==============
But do remember that worksheet protection is not really a security setting.
It's easily broken. So don't put stuff you don't want to share on any worksheet
of any workbook you're gonna share with others.

The VBA project protection isn't secure either. But most users don't even know
it exists (in my experience).



Steve E wrote:

Sheesh. How long did it take for you to learn this stuff...

Anyway... you da man.

The D18:D34 range was wrong... should have been D18:D32 this is a range
where a user enters a product model and the adjacent cells (L:V) are
variables entered dependent on the specific model selected (and therefore
entered) in the D cell. All of the inputs for L:V are dyanmic lists based on
the input to the left...

Now I have one more stupid question.

I have a form button on the top of this worksheet to allow a user to clear
out all of the information that they've entered (to make double sure that
there aren't any residual inputs that might screw them up down the road.

I recorded a macro to clear all of the necessary ranges and it worked fine
until I protect the sheet... then the macro prompts my user for a password...
which kind of defeats some of the reason that I have the sheet protected in
the first place [realizing that someone who prowls this usergroup already
knows how to crack thru the password sheet protection]... how can I convert
my macro to VBA and assign it to a command button?

Sub UnprotectClearFormProtect()
'
' UnprotectClearFormProtect Macro
' Macro recorded 9/1/2006 by Steven W. Edwards
'

'
ActiveSheet.Unprotect
Range("D1:D2,D9:G9,D12:G12,J1:L9,C18:D32,G18:V32,C 37:G43").Select
Range("C37:D42").Activate
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Thanks again for all of your help. You (and the rest of the folks that make
this user group go) rock!

Steve

"Dave Peterson" wrote:

Yep. Each procedure has to have its own name in any module. And since these
are defined by excel (as event procedures), you can't just rename one and have
it work.

But you can combine them into one.

It looks like the first procedure just checks for changes in D18:D34. If the
change is made there, it clears the contents of L:V of the same row.

This is the way I'd write that procedu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("D18:d34")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Me.Cells(Target.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True

End Sub

The .resize(1,11) stuff means to take the original range (L samerow as change)
and resize it to 1 row by 11 columns (L to V).

(This isn't the end product--just an aside.)


I think this may do both of the things you want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
If Not Intersect(Me.Range("D18:d34"), .Cells) Is Nothing Then
Application.EnableEvents = False
Me.Cells(.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True
End If
End With
End Sub

But I am confused about the differences in ranges.

You check rows 18:32 and 37:43 in one section and 18:34 in the other.

Seems kind of weird, but maybe that's what fits your data????

Steve E wrote:

Dave,

This is great!

Only problem is that I forgot about another Worksheet_Change event that I
have in this module:

'Private Sub Worksheet_Change(ByVal Target As Range)
' Dim Rchange As Integer
' Rchange = Target.Row ' row number selected
' If Rchange 17 And Rchange < 35 Then ' make sure only applies to
rows 18 to 34
' If Target.Address = "$D" & "$" & Rchange Then
' ' MsgBox "Target address changed : " & Target.Address
' Range("L" & Rchange, "V" & Rchange).ClearContents
' ' clears cells for in this row for cols L to T
' ' MsgBox "Range: " & " L" & Rchange & " to " & " T" & Rchange
& " Cleared" ' optional
' End If
' Else
' End If
'End Sub

when I leave it "in" I get an error "ambiguous name detected :
Worksheet_Change

??

Thanks in advance.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


You may want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
me.unprotect password:="Hi"
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
me.protect password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub

If d1 is locked on that protected sheet.


Steve E wrote:

Hi,

I tried to figure out how to make the code that JE McGimpsey has posted on
his site: http://www.mcgimpsey.com/excel/timestamp.html "DateStampMacro" but
just do not have the VBA skills to decipher how I need to change it to work
for me.

I'm trying to have a date time stamp in cell "D1" whenever my user changes
data in either of two ranges "C18:V32" and "C37:D43".

This is a customer tool for pricing and I want to record the date that they
changed inputs that affect price.

This is xl2003

These ranges have all kinds of data validation rules that are generated from
sheets that are protected and this workbook and worksheet will also be
protected to keep the user from overrighting formulas etc.

Thanks in advance!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

All I can really say is "THANKS".

Hope you have a great (and safe) holiday!

Steve

"Dave Peterson" wrote:

Change your code to include the password.

ActiveSheet.Unprotect password:="hi"
'do the work
ActiveSheet.Protect password:="hi", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

====
And remember to protect the project from snoops.

Inside the VBE
tools|VBA Project Properties
Protection tab

(give it a memorable password).

==============
But do remember that worksheet protection is not really a security setting.
It's easily broken. So don't put stuff you don't want to share on any worksheet
of any workbook you're gonna share with others.

The VBA project protection isn't secure either. But most users don't even know
it exists (in my experience).



Steve E wrote:

Sheesh. How long did it take for you to learn this stuff...

Anyway... you da man.

The D18:D34 range was wrong... should have been D18:D32 this is a range
where a user enters a product model and the adjacent cells (L:V) are
variables entered dependent on the specific model selected (and therefore
entered) in the D cell. All of the inputs for L:V are dyanmic lists based on
the input to the left...

Now I have one more stupid question.

I have a form button on the top of this worksheet to allow a user to clear
out all of the information that they've entered (to make double sure that
there aren't any residual inputs that might screw them up down the road.

I recorded a macro to clear all of the necessary ranges and it worked fine
until I protect the sheet... then the macro prompts my user for a password...
which kind of defeats some of the reason that I have the sheet protected in
the first place [realizing that someone who prowls this usergroup already
knows how to crack thru the password sheet protection]... how can I convert
my macro to VBA and assign it to a command button?

Sub UnprotectClearFormProtect()
'
' UnprotectClearFormProtect Macro
' Macro recorded 9/1/2006 by Steven W. Edwards
'

'
ActiveSheet.Unprotect
Range("D1:D2,D9:G9,D12:G12,J1:L9,C18:D32,G18:V32,C 37:G43").Select
Range("C37:D42").Activate
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Thanks again for all of your help. You (and the rest of the folks that make
this user group go) rock!

Steve

"Dave Peterson" wrote:

Yep. Each procedure has to have its own name in any module. And since these
are defined by excel (as event procedures), you can't just rename one and have
it work.

But you can combine them into one.

It looks like the first procedure just checks for changes in D18:D34. If the
change is made there, it clears the contents of L:V of the same row.

This is the way I'd write that procedu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("D18:d34")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Me.Cells(Target.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True

End Sub

The .resize(1,11) stuff means to take the original range (L samerow as change)
and resize it to 1 row by 11 columns (L to V).

(This isn't the end product--just an aside.)


I think this may do both of the things you want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
If Not Intersect(Me.Range("D18:d34"), .Cells) Is Nothing Then
Application.EnableEvents = False
Me.Cells(.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True
End If
End With
End Sub

But I am confused about the differences in ranges.

You check rows 18:32 and 37:43 in one section and 18:34 in the other.

Seems kind of weird, but maybe that's what fits your data????

Steve E wrote:

Dave,

This is great!

Only problem is that I forgot about another Worksheet_Change event that I
have in this module:

'Private Sub Worksheet_Change(ByVal Target As Range)
' Dim Rchange As Integer
' Rchange = Target.Row ' row number selected
' If Rchange 17 And Rchange < 35 Then ' make sure only applies to
rows 18 to 34
' If Target.Address = "$D" & "$" & Rchange Then
' ' MsgBox "Target address changed : " & Target.Address
' Range("L" & Rchange, "V" & Rchange).ClearContents
' ' clears cells for in this row for cols L to T
' ' MsgBox "Range: " & " L" & Rchange & " to " & " T" & Rchange
& " Cleared" ' optional
' End If
' Else
' End If
'End Sub

when I leave it "in" I get an error "ambiguous name detected :
Worksheet_Change

??

Thanks in advance.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


You may want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
me.unprotect password:="Hi"
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
me.protect password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub

If d1 is locked on that protected sheet.


Steve E wrote:

Hi,

I tried to figure out how to make the code that JE McGimpsey has posted on
his site: http://www.mcgimpsey.com/excel/timestamp.html "DateStampMacro" but
just do not have the VBA skills to decipher how I need to change it to work
for me.

I'm trying to have a date time stamp in cell "D1" whenever my user changes
data in either of two ranges "C18:V32" and "C37:D43".

This is a customer tool for pricing and I want to record the date that they
changed inputs that affect price.

This is xl2003

These ranges have all kinds of data validation rules that are generated from
sheets that are protected and this workbook and worksheet will also be
protected to keep the user from overrighting formulas etc.

Thanks in advance!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

You're welcome and you have a good weekend, too.

Steve E wrote:

All I can really say is "THANKS".

Hope you have a great (and safe) holiday!

Steve

"Dave Peterson" wrote:

Change your code to include the password.

ActiveSheet.Unprotect password:="hi"
'do the work
ActiveSheet.Protect password:="hi", DrawingObjects:=True, _
Contents:=True, Scenarios:=True

====
And remember to protect the project from snoops.

Inside the VBE
tools|VBA Project Properties
Protection tab

(give it a memorable password).

==============
But do remember that worksheet protection is not really a security setting.
It's easily broken. So don't put stuff you don't want to share on any worksheet
of any workbook you're gonna share with others.

The VBA project protection isn't secure either. But most users don't even know
it exists (in my experience).



Steve E wrote:

Sheesh. How long did it take for you to learn this stuff...

Anyway... you da man.

The D18:D34 range was wrong... should have been D18:D32 this is a range
where a user enters a product model and the adjacent cells (L:V) are
variables entered dependent on the specific model selected (and therefore
entered) in the D cell. All of the inputs for L:V are dyanmic lists based on
the input to the left...

Now I have one more stupid question.

I have a form button on the top of this worksheet to allow a user to clear
out all of the information that they've entered (to make double sure that
there aren't any residual inputs that might screw them up down the road.

I recorded a macro to clear all of the necessary ranges and it worked fine
until I protect the sheet... then the macro prompts my user for a password...
which kind of defeats some of the reason that I have the sheet protected in
the first place [realizing that someone who prowls this usergroup already
knows how to crack thru the password sheet protection]... how can I convert
my macro to VBA and assign it to a command button?

Sub UnprotectClearFormProtect()
'
' UnprotectClearFormProtect Macro
' Macro recorded 9/1/2006 by Steven W. Edwards
'

'
ActiveSheet.Unprotect
Range("D1:D2,D9:G9,D12:G12,J1:L9,C18:D32,G18:V32,C 37:G43").Select
Range("C37:D42").Activate
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Thanks again for all of your help. You (and the rest of the folks that make
this user group go) rock!

Steve

"Dave Peterson" wrote:

Yep. Each procedure has to have its own name in any module. And since these
are defined by excel (as event procedures), you can't just rename one and have
it work.

But you can combine them into one.

It looks like the first procedure just checks for changes in D18:D34. If the
change is made there, it clears the contents of L:V of the same row.

This is the way I'd write that procedu

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub

If Intersect(Target, Me.Range("D18:d34")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Me.Cells(Target.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True

End Sub

The .resize(1,11) stuff means to take the original range (L samerow as change)
and resize it to 1 row by 11 columns (L to V).

(This isn't the end product--just an aside.)


I think this may do both of the things you want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
If Not Intersect(Me.Range("D18:d34"), .Cells) Is Nothing Then
Application.EnableEvents = False
Me.Cells(.Row, "L").Resize(1, 11).ClearContents
Application.EnableEvents = True
End If
End With
End Sub

But I am confused about the differences in ranges.

You check rows 18:32 and 37:43 in one section and 18:34 in the other.

Seems kind of weird, but maybe that's what fits your data????

Steve E wrote:

Dave,

This is great!

Only problem is that I forgot about another Worksheet_Change event that I
have in this module:

'Private Sub Worksheet_Change(ByVal Target As Range)
' Dim Rchange As Integer
' Rchange = Target.Row ' row number selected
' If Rchange 17 And Rchange < 35 Then ' make sure only applies to
rows 18 to 34
' If Target.Address = "$D" & "$" & Rchange Then
' ' MsgBox "Target address changed : " & Target.Address
' Range("L" & Rchange, "V" & Rchange).ClearContents
' ' clears cells for in this row for cols L to T
' ' MsgBox "Range: " & " L" & Rchange & " to " & " T" & Rchange
& " Cleared" ' optional
' End If
' Else
' End If
'End Sub

when I leave it "in" I get an error "ambiguous name detected :
Worksheet_Change

??

Thanks in advance.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


You may want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
me.unprotect password:="Hi"
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
me.protect password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub

If d1 is locked on that protected sheet.


Steve E wrote:

Hi,

I tried to figure out how to make the code that JE McGimpsey has posted on
his site: http://www.mcgimpsey.com/excel/timestamp.html "DateStampMacro" but
just do not have the VBA skills to decipher how I need to change it to work
for me.

I'm trying to have a date time stamp in cell "D1" whenever my user changes
data in either of two ranges "C18:V32" and "C37:D43".

This is a customer tool for pricing and I want to record the date that they
changed inputs that affect price.

This is xl2003

These ranges have all kinds of data validation rules that are generated from
sheets that are protected and this workbook and worksheet will also be
protected to keep the user from overrighting formulas etc.

Thanks in advance!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Goodday,

I have used the macro on http://www.mcgimpsey.com/excel/timestamp.htm and
it works fine. I only would like to add something. When I make a change to a
cell, I want the time of the update in 1 cell but I also would like to have
the old value in another cell.

Can anyone help me with how I can do this? I am not too good with this VBA.

Thnks!

RMF



"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


You may want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
me.unprotect password:="Hi"
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
me.protect password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub

If d1 is locked on that protected sheet.


Steve E wrote:

Hi,

I tried to figure out how to make the code that JE McGimpsey has posted on
his site: http://www.mcgimpsey.com/excel/timestamp.html "DateStampMacro" but
just do not have the VBA skills to decipher how I need to change it to work
for me.

I'm trying to have a date time stamp in cell "D1" whenever my user changes
data in either of two ranges "C18:V32" and "C37:D43".

This is a customer tool for pricing and I want to record the date that they
changed inputs that affect price.

This is xl2003

These ranges have all kinds of data validation rules that are generated from
sheets that are protected and this workbook and worksheet will also be
protected to keep the user from overrighting formulas etc.

Thanks in advance!

--

Dave Peterson

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
Me.Unprotect Password:="Hi"
With Me.Range("D1")
'move the old value over one column (to E1)
.Offset(0, 1).Value = .Value
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Me.Protect Password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub




RMF wrote:

Goodday,

I have used the macro on http://www.mcgimpsey.com/excel/timestamp.htm and
it works fine. I only would like to add something. When I make a change to a
cell, I want the time of the update in 1 cell but I also would like to have
the old value in another cell.

Can anyone help me with how I can do this? I am not too good with this VBA.

Thnks!

RMF

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub


You may want:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
me.unprotect password:="Hi"
With Me.Range("D1")
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
me.protect password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub

If d1 is locked on that protected sheet.


Steve E wrote:

Hi,

I tried to figure out how to make the code that JE McGimpsey has posted on
his site: http://www.mcgimpsey.com/excel/timestamp.html "DateStampMacro" but
just do not have the VBA skills to decipher how I need to change it to work
for me.

I'm trying to have a date time stamp in cell "D1" whenever my user changes
data in either of two ranges "C18:V32" and "C37:D43".

This is a customer tool for pricing and I want to record the date that they
changed inputs that affect price.

This is xl2003

These ranges have all kinds of data validation rules that are generated from
sheets that are protected and this workbook and worksheet will also be
protected to keep the user from overrighting formulas etc.

Thanks in advance!

--

Dave Peterson


--

Dave Peterson

Thnks dave,

I cannot make it do what I want however. I have two colums (H2:H150) and
(I2:I150). The first thing I want is that whenever I make a change in one of
the cells in these two ranges, the cell in the same row to be time stamped.
(e.g. I make a change in cell H15 so the cell in Q15 should be stamped. if i
make a change in cell I15 also Q15 should be stamped.

Secondly, I want the old value in the cell to be put in another cell. (e.g.
If i make a change in H15 I want the old value in R15 and if I make a change
to I15 I want the old value in S15).

Can anyone help me with this? Thnks a lot in advance!

RMF

p.s.
The code I use until now is this:
====================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("H2:H150"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 24).ClearContents
Else
With .Offset(0, 24)
.NumberFormat = "dd/mm hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("I2:I150"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 24).ClearContents
Else
With .Offset(0, 24)
.NumberFormat = "dd/mm hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
=============================

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
Me.Unprotect Password:="Hi"
With Me.Range("D1")
'move the old value over one column (to E1)
.Offset(0, 1).Value = .Value
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Me.Protect Password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub




RMF wrote:

Goodday,

I have used the macro on http://www.mcgimpsey.com/excel/timestamp.htm and
it works fine. I only would like to add something. When I make a change to a
cell, I want the time of the update in 1 cell but I also would like to have
the old value in another cell.

Can anyone help me with how I can do this? I am not too good with this VBA.

Thnks!

maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim DateTimeCell As Range
Dim OldValCell As Range
Dim OldVal As Variant
Dim CurValue As String

With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("H2:i150"), .Cells) Is Nothing Then
Set DateTimeCell = Me.Cells(.Row, "Q")
Set OldValCell = .Offset(0, 10)
CurValue = .Value

With Application
.EnableEvents = False
.Undo
End With

OldVal = .Value
.Value = CurValue

With DateTimeCell
.NumberFormat = "dd/mm hh:mm"
.Value = Now
End With

OldValCell.Value = OldVal
Application.EnableEvents = True
End If
End With
End Sub

RMF wrote:

Thnks dave,

I cannot make it do what I want however. I have two colums (H2:H150) and
(I2:I150). The first thing I want is that whenever I make a change in one of
the cells in these two ranges, the cell in the same row to be time stamped.
(e.g. I make a change in cell H15 so the cell in Q15 should be stamped. if i
make a change in cell I15 also Q15 should be stamped.

Secondly, I want the old value in the cell to be put in another cell. (e.g.
If i make a change in H15 I want the old value in R15 and if I make a change
to I15 I want the old value in S15).

Can anyone help me with this? Thnks a lot in advance!

RMF

p.s.
The code I use until now is this:
====================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("H2:H150"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 24).ClearContents
Else
With .Offset(0, 24)
.NumberFormat = "dd/mm hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("I2:I150"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 24).ClearContents
Else
With .Offset(0, 24)
.NumberFormat = "dd/mm hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
=============================

"Dave Peterson" wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("C18:V32,C37:D43"), .Cells) Is Nothing Then
Application.EnableEvents = False
Me.Unprotect Password:="Hi"
With Me.Range("D1")
'move the old value over one column (to E1)
.Offset(0, 1).Value = .Value
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
Me.Protect Password:="Hi"
Application.EnableEvents = True
End If
End With
End Sub




RMF wrote:

Goodday,

I have used the macro on http://www.mcgimpsey.com/excel/timestamp.htm and
it works fine. I only would like to add something. When I make a change to a
cell, I want the time of the update in 1 cell but I also would like to have
the old value in another cell.

Can anyone help me with how I can do this? I am not too good with this VBA.

Thnks!




--

Dave Peterson

I have a question:

I've been using Dave's macro for a while now but there's one thing I wish I
could resolve...
The problem is a Debug error that occurs when I start to type in the cell
that triggers the timestamp but then decide to delete what I was typing and
leave the cell with no information. Is there something that can be added to
the following macro to have it keep the debug error from happening when I
leave the trigger cell blank after clicking in it?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("E11:E510"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -2).ClearContents
Else
With .Offset(0, -2)
.NumberFormat = "hh:mm"
.Value = Time
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Any help would be appreciated!

First, the code doesn't run when you start to change the cell--it fires when you
finish (hitting enter or tab or selecting another cell).

Which line causes the error?

Please share the exact details--addresses, values in the cells that are changing
and your keystrokes of what you're doing.

The only thing that I see that could be causing trouble is this line:

..Offset(0, -2).ClearContents

This will fail if that .offset(0,-2) cell is part of a merged set of cells.

If you're using merged cells, change this line to:

..Offset(0, -2).value = ""



PYO1012 wrote:

I have a question:

I've been using Dave's macro for a while now but there's one thing I wish I
could resolve...
The problem is a Debug error that occurs when I start to type in the cell
that triggers the timestamp but then decide to delete what I was typing and
leave the cell with no information. Is there something that can be added to
the following macro to have it keep the debug error from happening when I
leave the trigger cell blank after clicking in it?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("E11:E510"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -2).ClearContents
Else
With .Offset(0, -2)
.NumberFormat = "hh:mm"
.Value = Time
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Any help would be appreciated!

--

Dave Peterson

Thank you, Dave, for responding! I tried exactly what you suggested
"..Offset(0, -2).value = "" " and that worked perfectly. Thank you, again!!

"Dave Peterson" wrote:

First, the code doesn't run when you start to change the cell--it fires when you
finish (hitting enter or tab or selecting another cell).

Which line causes the error?

Please share the exact details--addresses, values in the cells that are changing
and your keystrokes of what you're doing.

The only thing that I see that could be causing trouble is this line:

..Offset(0, -2).ClearContents

This will fail if that .offset(0,-2) cell is part of a merged set of cells.

If you're using merged cells, change this line to:

..Offset(0, -2).value = ""



PYO1012 wrote:

I have a question:

I've been using Dave's macro for a while now but there's one thing I wish I
could resolve...
The problem is a Debug error that occurs when I start to type in the cell
that triggers the timestamp but then decide to delete what I was typing and
leave the cell with no information. Is there something that can be added to
the following macro to have it keep the debug error from happening when I
leave the trigger cell blank after clicking in it?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("E11:E510"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -2).ClearContents
Else
With .Offset(0, -2)
.NumberFormat = "hh:mm"
.Value = Time
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Any help would be appreciated!

--

Dave Peterson
.

Merged cells can cause lots of trouble--I try not to use them.

PYO1012 wrote:

Thank you, Dave, for responding! I tried exactly what you suggested
"..Offset(0, -2).value = "" " and that worked perfectly. Thank you, again!!

"Dave Peterson" wrote:

First, the code doesn't run when you start to change the cell--it fires when you
finish (hitting enter or tab or selecting another cell).

Which line causes the error?

Please share the exact details--addresses, values in the cells that are changing
and your keystrokes of what you're doing.

The only thing that I see that could be causing trouble is this line:

..Offset(0, -2).ClearContents

This will fail if that .offset(0,-2) cell is part of a merged set of cells.

If you're using merged cells, change this line to:

..Offset(0, -2).value = ""



PYO1012 wrote:

I have a question:

I've been using Dave's macro for a while now but there's one thing I wish I
could resolve...
The problem is a Debug error that occurs when I start to type in the cell
that triggers the timestamp but then decide to delete what I was typing and
leave the cell with no information. Is there something that can be added to
the following macro to have it keep the debug error from happening when I
leave the trigger cell blank after clicking in it?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("E11:E510"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -2).ClearContents
Else
With .Offset(0, -2)
.NumberFormat = "hh:mm"
.Value = Time
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Any help would be appreciated!

--

Dave Peterson
.


--

Dave Peterson