Excel VBA: Worksheet_Change Cells.Protect

tinfanide · #1 04-04-2012, 04:15 AM

Quote:

Originally Posted by davesexcel

You would have to unlock all the cells first, and then lock the cells you want protected before you protect the sheet.

Well, nothing to do with Cells.Locked = False (I guess this is what you meant) in the above file. Please kindly open the file and see. I've used

Code:

Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True

to protect the column to be protected. But it stops working when I try to delete the cell in that column.

tinfanide · #2 04-05-2012, 03:19 AM

Application Data.xlsm

Please take a look at the attached file.
I have tried some codes but it fails.
I just want the 'Date' Column to be protected without having the sheet protected (otherwise, new values cannot be added to the table)

caholmes · #3 04-06-2012, 06:21 PM

Hi Tinfanide,

unfortunately you can't have your cake and eat it too. The only way to protect data on a sheet using the excel tools is to protect the sheet. This causes the locked property to be enforced for each cell. You could possibly protect and unprotect the sheet if it meets a condition using the Worksheet_SelectionChange event. It could look something like this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsEmpty(Target) Then
ActiveSheet.Unprotect Password:="dog"
Else
ActiveSheet.Protect Password:="dog", DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub

Alternatively you could password protect a range (Allow uses to edit ranges) located on the Review Tab however you would still need to protect and unprotect the data based on a condition. This method may just give you a bit more control over the data.

I hope this helps!

Catalin.B · #4 04-06-2012, 10:37 PM

Another approach i to protect all completed cells when closing the workbook, this way the code will not run at every change, slowing down work.

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim LastRow, LastColumn
Dim LastCell As String

Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="1234"

With ActiveSheet
        If WorksheetFunction.CountA(Cells) > 0 Then
          LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row
        End If
  If WorksheetFunction.CountA(Cells) > 0 Then
    LastColumn = Cells.Find(What:="*", After:=[A1], _
       SearchOrder:=xlByColumns, _
       SearchDirection:=xlPrevious).Column
  End If
    
     LastCell = "A1:" & Split(Columns(LastColumn).Address, "$")(2) & LastRow
   
   .Range(LastCell).Cells.Locked = False

    For Each cell In .Range(LastCell).Cells
         If Not IsEmpty(cell) Then
             cell.Locked = True
         End If
    Next cell

ActiveSheet.Protect Password:="1234", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End With
End Sub

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