Convert a date to text and protect that column

[gilbertbyproxy]

Hi All

I am failing but still trying to write a macro or formula to change NOW() in to text, in a cell so when I view it in future, it will remain at the date when the data was entered in to that column.

For this I am currently using

M50=COUNTIF(M5:M49,"")
M4=IF(M50<>44,NOW(),"")

The following week I and or others are entering data into the next column only and so on. All the dates are in Range("F4:IK4")

I also need to protect the last/all previous columns containing data when I close the workbook.

I have tried a circular formula for the date but other people need to input data as well and it reminds you that it is there all the time.

M4=IF(AND(M4="",NOT(ISBLANK(M7))),NOW(),IF(ISBLANK(M7),"",M4))

I unsuccessfully tried a macro for the date

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Range("F4:IK4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
End Sub

but this removes the formulas from the cells to be filled in at a later date.

Unfortunately I don't have access to the workbook until the 16th June.

Thanks in advance.

 

[p45cal]

something like this:

With Sheets("sheet1") 'adjust sheet name
    .Protect userinterfaceonly:=True    ' this may already be elsewhere in another module.
    For Each cll In .Range("F4:IK4").Cells
        If cll.Value <> "" Then 'if is not empty then…
            cll.Value = cll.Value 'fix the value
            cll.Resize(46).Locked = True 'lock the cell and the ones below it.
        End If
    Next cll
End With

 

[gilbertbyproxy]

You Genius, I bow down to your mystical Excel ability.

 

[gilbertbyproxy]

Sorry to pester you again but I have to password the sheets but I am missing something, can you help?
This is what I have so far......

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet 1", "Sheet 2").Unprotect Password:="password"
With Sheets("Sheet 1")
For Each cll In .Range("F4:IK4").Cells
If cll.Value <> "" Then
cll.Value = cll.Value
cll.Resize(46).Locked = True
End If
Next cll
End With
With Sheets("Sheet 2")
For Each cll In .Range("F4:IK4").Cells
If cll.Value <> "" Then
cll.Value = cll.Value
cll.Resize(33).Locked = True
End If
Next cll
End With
Sheets("Sheet 1", "Sheet 2").Protect Password:="password"
End Sub

 

[gilbertbyproxy]

Cracked it

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet 1").Unprotect Password:="password"
Sheets("Sheet 2").Unprotect Password:="password"
With Sheets("Sheet 1")
For Each cll In .Range("F4:IK4").Cells
If cll.Value <> "" Then
cll.Value = cll.Value
cll.Resize(46).Locked = True
End If
Next cll
End With
With Sheets("Sheet 2")
For Each cll In .Range("F4:IK4").Cells
If cll.Value <> "" Then
cll.Value = cll.Value
cll.Resize(33).Locked = True
End If
Next cll
End With
Sheets("Sheet 1").Protect Password:="password"
Sheets("Sheet 2").Protect Password:="password"
End Sub