Auto Lock Cells after Data Entry AND Save

[ROSH22]

Firstly, I know 0 about programming or VBA, but I found this code which auto locks cells after you enter data in them.









Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Place this code in the worksheet code module. ' The subroutine unprotects the sheet and IF an entry is made ' in an empty ("") cell, the cell is locked and then the ' sheet's protection is turned back on. Any further ' attempts to edit the cell generate the password msgbox. ' You can set the range to one cell ("A1") or an area ("A1:Z300"). ' 1. Use Format - Cells - Protection to unlock the cells ' in the range where one time entries are to be allowed. ' 2. Protect the worksheet with the same password as ' you use twice in the following subroutine (thepassword).
On Error GoTo justenditall
Application.EnableEvents = False If Not Intersect(Target, Range("A1:A20")) Is Nothing Then If Target.Value <> "" Then ActiveSheet.Unprotect Password:="thepassword" Target.Locked = True End If End If
ActiveSheet.Protect Password:="thepassword"
justenditall: Application.EnableEvents = True End Sub






Now I want to make this only work when the file is saved. For example, when an employee opens up the file he or she is able to enter any new data, but not edit old data, and once saved, they will not be able to edit the data they just entered.
I hope this makes sense.
Thanks!

 

[Worf]

Welcome to the Board

- The version below will tell you when the code is executed.
- Did you enable macros when opening the file?
- Note that cells will be locked only if they are not blank.

Dim wr As Range, wcell As Range


' this code goes at ThisWorkbook module     <--------


Private Sub Workbook_Activate()
    Initial
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set wr = ThisWorkbook.Worksheets("S2").[a1:a20]           ' editable range
For Each wcell In wr
    Select Case wcell.Interior.ColorIndex
        Case xlNone
            ' no action
        Case Else
             wcell.Interior.ColorIndex = xlNone
             If wcell <> "" Then wcell.Locked = True      ' no more changes
    End Select
Next: MsgBox "Save event was triggered"
End Sub


Private Sub Workbook_Open()
    Initial
End Sub


Sub Initial()
ThisWorkbook.Worksheets("S2").Protect Password:="pw", userinterfaceonly:=True
Set wr = ThisWorkbook.Worksheets("S2").[a1:a20]
wr.Interior.ColorIndex = xlNone
For Each wcell In wr
    wcell.Locked = False
    If wcell <> "" Then wcell.Locked = True
Next
MsgBox "Initial has executed"
End Sub

 

[paroduso]

Welcome to the Board

- The version below will tell you when the code is executed.
- Did you enable macros when opening the file?
- Note that cells will be locked only if they are not blank.

Dim wr As Range, wcell As Range


' this code goes at ThisWorkbook module     <--------


Private Sub Workbook_Activate()
    Initial
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set wr = ThisWorkbook.Worksheets("S2").[a1:a20]           ' editable range
For Each wcell In wr
    Select Case wcell.Interior.ColorIndex
        Case xlNone
            ' no action
        Case Else
             wcell.Interior.ColorIndex = xlNone
             If wcell <> "" Then wcell.Locked = True      ' no more changes
    End Select
Next: MsgBox "Save event was triggered"
End Sub


Private Sub Workbook_Open()
    Initial
End Sub


Sub Initial()
ThisWorkbook.Worksheets("S2").Protect Password:="pw", userinterfaceonly:=True
Set wr = ThisWorkbook.Worksheets("S2").[a1:a20]
wr.Interior.ColorIndex = xlNone
For Each wcell In wr
    wcell.Locked = False
    If wcell <> "" Then wcell.Locked = True
Next
MsgBox "Initial has executed"
End Sub

working fine but only protecting cells if tkey are filled...can you make it to protect full row regardless of cells whether it is filled or not

when i say full row its a range form b6:cf20


thank you.

 

[Worf]

Do you still need help?

 

[tania_b]

Hi guys,
I'm totally new to VBA, and i really need to make a vacation planner in excel that will allow an employee to open the file, enter specific data (A or FR), but not to edit old data, and once saved, they will not be able to edit the data they just entered. Data range is B3:AF200.
Can you please help me?
Thank you!

 

[tania_b]

I have tried the code you wrote for Rosh22 and it actually works, but if , let's say, you make a mistake and delete the entry, the cells change their color (probably because excel considers they were edited) and i don't want that. And also, the code changes my excel formatting colors. Is here a way to stop that?
Thank you!