Excel VBA: Worksheet_Change Cells.Protect

tinfanide · #7 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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Protect / Lock Cells / Print Set-up?	meggenm	Excel	3	01-26-2012 09:57 PM
protect and unprotect in shared excel	mark-gabb	Excel	0	01-09-2012 07:30 PM
Excel 2010 Format cells to use as Gantt or Plan	Kizzie	Excel	0	12-13-2011 02:31 AM
password protect excel file?	Dawg751	Excel	2	01-28-2010 06:23 AM
Importing individual cells from Excel to Word	themangoagent	Word	1	10-12-2009 08:14 AM