How to auto lock cells after data entry and save

[Ganesh Lingayat]

Hi, I have a table range A3:T1048576 in a data entry worksheet… the file will be sat on a shared drive and will be accessed by different people throughout the day. I want the data entry worksheet to be locked and protected so people can only access and enter data into cells A3:T1048576 in the table. When they have entered data into chosen cells and then saved the file, I want the cells they have entered data into to be locked so the next person opening the file can’t access them and make any changes.

Is there some VB code I can input into the worksheet that will execute this locking out of the cells when the user saves the file? Am using Excel 2013. please find file attach for reference
Many thanks indeed for your help…

 

[Luke M]

In the ThisWorkbook module of VB, put this in

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'What is the password for sheet?
Const myPass As String = "1234"

'Which sheet are we working with?
With Worksheets("Sheet1")
    'Unprotect from previous state
    .Unprotect myPass
    'Lock any cells that have data in them
    .Range("A:T").SpecialCells(xlCellTypeConstants).Locked = True
    'Reprotect the sheet
    .Protect myPass
End With
End Sub

Change the names/password as desired to fit.

 

[Ganesh Lingayat]

In the ThisWorkbook module of VB, put this in

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'What is the password for sheet?
Const myPass As String = "1234"

'Which sheet are we working with?
With Worksheets("Sheet1")
    'Unprotect from previous state
    .Unprotect myPass
    'Lock any cells that have data in them
    .Range("A:T").SpecialCells(xlCellTypeConstants).Locked = True
    'Reprotect the sheet
    .Protect myPass
End With
End Sub

Change the names/password as desired to fit.

Thank you very much Luke M.

this work brilliantly but there are some restriction while data entry if any one make a mistake and save the file he cannot edit that while entering the data.
is this possible that Auto Lock cells can be done on date wise. like if we entered any data today, it will be editable before today's date changed. but this same data cannot be editable on tomorrow.

please help me

thanks in advance

 

[Luke M]

It might be possible to use a helper worksheet that stored a date stamp for every cell, keeping track of when they were edited...but it would be messy, IMO.
Could policy be setup that the file gets backed-up/archived every night, thus preserving the data? Or perhaps give person ability to unprotect the sheet? I'm not sure how much effort we want to make someone put forth to unprotect the sheet.

 

[Sujit10]

How do I apply the code mentioned above to all the worksheets in my workbook?

 

[Luke M]

Hi Sujit,

To lock all worksheets in the workbook, we can loop over the collection.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'What is the password for sheet?
Const myPass As String = "1234"
Dim ws As Worksheet

'Loop through all worksheets
For Each ws In ThisWorkbook.Worksheets
    With ws
        'Unprotect from previous state
       .Unprotect myPass
        'Lock any cells that have data in them
       .Range("A:T").SpecialCells(xlCellTypeConstants).Locked = True
        'Reprotect the sheet
       .Protect myPass
    End With
Next ws
End Sub

 

[Sing]

Want to lock a particular cell in a Column once the data is entered & rest of the cells to be unlocked until the data is entered.

Note: Dose not want to lock the entire worksheet.

Can some one assist in VB coding.

 

[sanju2008@]

how can i autolock cells after data entry in a workbook containing 300 sheets