Microsoft Office Forums - View Single Post - [Solved] Excel VBA: Worksheet_Change Cells.Protect

tinfanide · 04-07-2012, 07:13 AM

Thanks for all the input from you guys.
Really helpful and inspiring.
Here's the adapted version in my case:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Columns(2)) Is Nothing Then
    If Not IsEmpty(Cells(Target.Row, 2)) Then
        With Cells(Target.Row, 1)
            If .Value < Date Then GoTo Break
            .Value = Format(Date, "dd mmmm yyyy")
            .NumberFormat = "[$-809]dd mmmm yyyy;@"
        End With
    End If
End If

Break:
ActiveSheet.Unprotect
If Not Intersect(Target, Columns(1)) Is Nothing Then
    ActiveSheet.Cells.Locked = False
    Selection.Locked = True
    ActiveSheet.Protect
End If

End Sub

Book2.xlsm
Please look at the codes after the Break label in the attachment.
It works like
when any cells in Column 1 is selected / active,
they are protected.
But when it's not active,
other cells are free to edit.
Of course when you type a formula in areas except Column 1 and that formula will affect cells in Column 1, Column 1 will still be affected.
It's not perfect, but it achieves what I want, mostly.