Dates

[Gord Dibben]

In that case, post your amended code as you have it currently.

Gord

 

[Josh]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 5 Or Target.Cells.Column = 8 Or
Target.Cells.Column = 11 Then
ActiveSheet.Unprotect Password:="justme"
For Each cell In Target
If cell.Value <> "" Then
With cell.Offset(0, 1)
.Value = Now
.Locked = True
End With
End If
Next
End If
enditall:
Application.EnableEvents = True
'ActiveSheet.Protect Password:="justme"
End Sub

I figured out my previous problem but now I want the cells that the dates
were inserted into to be locked but not having the entire sheet locked.
Thats why I put the ' in the second to last line of code. Is there any way I
can lock certain cells that have the date inserted into? For example, having
the cells in column 6 be locked after the date has appeared on the
spreadsheet.

 

[Dave Peterson]

The bad news is that you can lock any old cell you want to. But it really won't
mean much to the user until you protect the sheet. If the sheet is unprotected,
the user can do anything he/she wants to any cell--whether it's locked or
unlocked.

An alternative to your _change event:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Intersect(Target, Me.Range("e:e,h:h,k:k"))
If myRng Is Nothing Then
Exit Sub
End If

On Error GoTo EndItAll:
For Each myCell In myRng.Cells
If myCell.Value <> "" Then
With myCell.Offset(0, 1)
.Value = Now
.Locked = True
End With
End If
Next myCell

EndItAll:
Application.EnableEvents = True
Me.Protect Password:="justme"

End Sub

 

[Gord Dibben]

You cannot have any cells locked without protecting the worksheet.

You can format them as locked but won't be until you do the protection step.

See Dave's response for good info and some new code.


Gord Dibben MS Excel MVP