I
Invoice
Hi,
I'm using the following code for an automated date stamp. Whenever dat
is found cells (AI37:IV37), the two rows above automatically insert th
day number (dd) and month (mmm), and whenever data is deleted from tha
range, the day and month are deleted.
I'd like to protect the (ai37:iv37) cells in which the day and mont
are stamped, however, and I don't know how to change the code. The
should become protected after the date stamp is made if the cell belo
in the ai37:iv37 range was = "Attendance". If that cell then changed t
a blank or anything else, they should be unprotected to allow the use
to edit the date.
I hope this is not too much trouble. Thanks.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("AI37:IV37"), .Cells) Is Nothin
Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(-1, 0).ClearContents
.Offset(-2, 0).ClearContents
Else
With .Offset(-1, 0)
.NumberFormat = "dd"
.Value = Date
End With
With .Offset(-2, 0)
.NumberFormat = "mmm"
.Value = Date
End With
End If
Application.EnableEvents = True
End If
End With
End Su
I'm using the following code for an automated date stamp. Whenever dat
is found cells (AI37:IV37), the two rows above automatically insert th
day number (dd) and month (mmm), and whenever data is deleted from tha
range, the day and month are deleted.
I'd like to protect the (ai37:iv37) cells in which the day and mont
are stamped, however, and I don't know how to change the code. The
should become protected after the date stamp is made if the cell belo
in the ai37:iv37 range was = "Attendance". If that cell then changed t
a blank or anything else, they should be unprotected to allow the use
to edit the date.
I hope this is not too much trouble. Thanks.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("AI37:IV37"), .Cells) Is Nothin
Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(-1, 0).ClearContents
.Offset(-2, 0).ClearContents
Else
With .Offset(-1, 0)
.NumberFormat = "dd"
.Value = Date
End With
With .Offset(-2, 0)
.NumberFormat = "mmm"
.Value = Date
End With
End If
Application.EnableEvents = True
End If
End With
End Su