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