problem with date stamp and protected cells

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
 
C

Carim

Hi,

Just after .Value = Date
add .Locked = True

and do not forget at the end of your code
to add
ActiveSheet.Protect

HTH
Cheers
Carim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top