S
Sam H
Hi,
I have the following text running as a macro in my spreadsheet, however I
now want to do two things, I have protected the cells where the date stamps
appear, therefore I need to know what and where I put the bit in my macro to
unprotect and re-prrotect the cells.
Also everytime it carries out the date stamping I would also like it to save
the entire spreadsheet.
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("H:H"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -5).ClearContents
Else
With .Offset(0, -5)
.NumberFormat = "dd/mm/yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
'New code
If Not Intersect(Range("H:H"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -4).ClearContents
Else
With .Offset(0, -4)
.NumberFormat = "hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
'New code
If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd/mm/yy hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
'New code
If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then
Application.EnableEvents = False
If UCase(Target.Value) = "CALL RESOLVED" Then
With .Offset(0, 1)
.NumberFormat = "dd/mm/yy hh:mm"
.Value = Now
End With
Else
With .Offset(0, 1)
.ClearContents
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
I have the following text running as a macro in my spreadsheet, however I
now want to do two things, I have protected the cells where the date stamps
appear, therefore I need to know what and where I put the bit in my macro to
unprotect and re-prrotect the cells.
Also everytime it carries out the date stamping I would also like it to save
the entire spreadsheet.
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("H:H"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -5).ClearContents
Else
With .Offset(0, -5)
.NumberFormat = "dd/mm/yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
'New code
If Not Intersect(Range("H:H"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -4).ClearContents
Else
With .Offset(0, -4)
.NumberFormat = "hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
'New code
If Not Intersect(Range("Y:Y"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd/mm/yy hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
'New code
If Not Intersect(Range("AG:AG"), .Cells) Is Nothing Then
Application.EnableEvents = False
If UCase(Target.Value) = "CALL RESOLVED" Then
With .Offset(0, 1)
.NumberFormat = "dd/mm/yy hh:mm"
.Value = Now
End With
Else
With .Offset(0, 1)
.ClearContents
End With
End If
Application.EnableEvents = True
End If
End With
End Sub