Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date stamp with protected cells problem
Hi,
I'm using the following code for an automated date stamp. Whenever data is found cells (AI37:IV37), the two rows above automatically insert the day number (dd) and month (mmm), and whenever data is deleted from that range, the day and month are deleted. I'd like to protect the (ai37:iv37) cells in which the day and month are stamped, however, and I don't know how to change the code. They should become protected after the date stamp is made if the cell below in the ai37:iv37 range was = "Attendance". If that cell then changed to a blank or anything else, they should be unprotected to allow the user 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 Nothing 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 Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with date stamp and protected cells | Excel Worksheet Functions | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
Date stamp formula results? Tricky problem? | Excel Discussion (Misc queries) | |||
Problem trying to move between cells on a protected worksheet. | Excel Discussion (Misc queries) | |||
Date/Time Stamp Macro Button Problem | Excel Programming |