Hi guys, I'm new in Excel VBA coding, I would like to make the data unchangeable after save. I have a template which has text in some cells and some cells are blank, i have lock the cells which has text and keep the blank cells unlocked for user input. After the user input, i need to lock also the cell which the data have been entered before close the workbook. Its that possible?
Protected and locked after the data entry using VBA code
-
-
-
Re: Protected and locked after the data entry using VBA code
Found this in my notes (comes from somewhere in the web), should do what you need. Copy it in the ThisWorkBook code module:
Code
Display MoreOption Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim i As Long Dim j As Long Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.DisplayAlerts = False If ActiveSheet.Name = "Sheet1" Then 'change name of sheet as needed 'Resume to next line if any error occurs On Error Resume Next Dim Cell As Range With ActiveSheet 'First of all unprotect the entire sheet .Unprotect Password:="" 'Now search for non blank cells and lock them and unlock blank cells For Each Cell In ActiveSheet.UsedRange i = Cell.Row j = Cell.Column If Cell.Value <> "" Then If Cell.Locked = False Then Cell.Locked = True End If End If Next Cell 'Protect with blank password, you can change it .Protect Password:="" End With Application.Calculation = xlCalculationAutomatic Application.DisplayAlerts = True Application.ScreenUpdating = True End If End Sub
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!