#1
|
|||
|
|||
Excel VBA: Worksheet_Change Cells.Protect
Application Data.xlsm
I want the Date Column to be locked once the date is auto added (added once and stay forever) In my codes, I've tried using Cells.Protect (deleted later) and at present using the codes (see in the file) Could anyone figure out why the codes cannot protect Column Date values? |
#2
|
||||
|
||||
You would have to unlock all the cells first, and then lock the cells you want protected before you protect the sheet.
|
#3
|
|||
|
|||
Quote:
Code:
Application.EnableEvents = False Application.Undo Application.EnableEvents = True |
#4
|
|||
|
|||
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) |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 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. |
#8
|
||||
|
||||
Interesting,
Looks like you had to to unlock all the cells first, and then lock the cells you want protected before you protect the sheet. |
#9
|
|||
|
|||
Quote:
Code:
Cells.Locked = False Columns(1).Locked = True ActiveSheet.Protect The second time when the sheet is protected, there should be an error when this code is run: Code:
Cells.Locked = False Code:
If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect End If '''''''''''''''''''''' Cells.Locked = False Columns(1).Locked = True ActiveSheet.Protect it's good practice first to check before you code. So "If a condition occurs do this; if not, do that." should be done always to avoid "strange" errors. It may prevent me from always asking questions. I can work it out sometimes if it is practised. |
|
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 |