lock cell after value is automatically placed when workbook is ope

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to lock a cell after todays date is placed into the cell. I want
this date to remain in the cell so when the application is re opened the
original date remains.

Does anyone have any ideas?

The script below that I am using falls over at the Selection.Locked = True
line of code.


Private Sub Workbook_Open()
If ActiveWorkbook.Name = "Status Report Template.xls" Then
NewNameforTemplate
End If
Application.DisplayAlerts = True
ThisWorkbook.Sheets("Form").Range("$E$12").Select
Selection.Value = Format(Now(), "dd mmm yyyy")
ThisWorkbook.Sheets("Form").Range("$E$12").Select
Selection.Locked = True
On Error Resume Next
End Sub
 
Damien said:
I am trying to lock a cell after todays date is placed into the cell. I want
this date to remain in the cell so when the application is re opened the
original date remains.

Does anyone have any ideas?

The script below that I am using falls over at the Selection.Locked = True
line of code.


Private Sub Workbook_Open()
If ActiveWorkbook.Name = "Status Report Template.xls" Then
NewNameforTemplate
End If
Application.DisplayAlerts = True
ThisWorkbook.Sheets("Form").Range("$E$12").Select
Selection.Value = Format(Now(), "dd mmm yyyy")
ThisWorkbook.Sheets("Form").Range("$E$12").Select
Selection.Locked = True
On Error Resume Next
End Sub
Click to expand...

you could replace this Selection.Value = Format(Now(), "dd mmm yyyy")
with these lines
If Selection.Locked = True Then Exit Sub ' do this to advoid an error
message if selection is locked & sheet protected.

If Selection.Value = "" Then Selection.Value = Format(Now(), "dd mmm
yyyy")

NOTE: remember locked only works if the sheet is protected.
 
Back
Top