Date Stamp

[Sam H]

Hi,

I managed to use the McGimpsey Macro to enter a date stamp into my
spreadsheet based on something being typed into an adjacent cell.

I want to then use the same thing for another part of my spreadsheet but
when I copy the macro and paste it back in, in doesn't work. I've tried
changing the name where it says PrivateSub Worksheet_Change to
Worksheet_Change2, but it still doesn't work. Can anyone help me?

Thanks

 

[Mike H]

Sam,

Events in Excel are pre-defined so you can't create another with

Worksheet_Change2

it won't execute. You will have to modify the existing event code to put the
timestamp somewhere else and there are ways of doing this. Post the code and
details of what you want and someone will help.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Sam H]

Hi, This is the code I have.

What I would actually like, is if anything is typed into column G then on
the same row the date is entered into Cell D (which is the -3 bit below) and
the Time in cell E. I would also like is that if "Call Resolved" is selected
in column AE then the Date and Time is entered into column AF.

Any help would be great.

Thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("G:G"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -3).ClearContents
Else
With .Offset(0, -3)
.NumberFormat = "dd/mm/yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

 

[Mike H]

Sam,

Not tested but this should combine the 2

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("G:G"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -3).ClearContents
Else
With .Offset(0, -3)
.NumberFormat = "dd/mm/yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
'New code
If Not Intersect(Range("AE:AE"), .Cells) Is Nothing Then
Application.EnableEvents = False
If UCase(Target.Value) = "CALL RESOLVED" Then
With .Offset(0, 1)
.NumberFormat = "dd/mm/yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Sam H]

Hi Mike,

That is excellent thank you so much, although is there any way of the second
bit have abit to clear cells, if we romve the Call Resolved from the cell.

thanks

 

[Mike H]

Hi,

try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("G:G"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -3).ClearContents
Else
With .Offset(0, -3)
.NumberFormat = "dd/mm/yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
'New code
If Not Intersect(Range("AE:AE"), .Cells) Is Nothing Then
Application.EnableEvents = False
If UCase(Target.Value) = "CALL RESOLVED" Then
With .Offset(0, 1)
.NumberFormat = "dd/mm/yy"
.Value = Now
End With
Else
With .Offset(0, 1)
.ClearContents
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Sam H]

Works perfectly, thank you so much.

Hi,

try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("G:G"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -3).ClearContents
Else
With .Offset(0, -3)
.NumberFormat = "dd/mm/yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
'New code
If Not Intersect(Range("AE:AE"), .Cells) Is Nothing Then
Application.EnableEvents = False
If UCase(Target.Value) = "CALL RESOLVED" Then
With .Offset(0, 1)
.NumberFormat = "dd/mm/yy"
.Value = Now
End With
Else
With .Offset(0, 1)
.ClearContents
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Your welcome
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.