Protect Worksheet Using Excel VBA

Often we need to protect our Excel worksheet in such a way that the important formulas and functions containing ranges should not be editable but other areas should be editable so that data entry operator does not delete or edit important data and makes less errors.
You can perform such a protection using standard ‘worksheet protection’ procedures but when you create a large project with a great deal of automation then you should use Excel VBA with inherent advantages.
The VBA code for protecting worksheets in Excel with a password is given below. You will also notice that the names and salaries can be edited but the calculation of perks and the total package data is locked and the formulas are hidden.
Sub protectmydata()
Dim strPassword As String
Range(“C2:D7”).Select
Selection.Locked = True
Selection.FormulaHidden = True
strPassword = InputBox(“Pls enter the password”)
ActiveSheet.Protect Password:=strPassword
End Sub

Now if you wish to lock and hide the formulas in all the cells in columns C and D and allow data entry in all the cells of columns A and B you can modify the above code as follows:
Sub protectmydata()
Dim strPassword As String
Columns(“C:D”).Select
Selection.Locked = True
Selection.FormulaHidden = True
Columns(“A:B”).Select
Selection.Locked = False
Selection.FormulaHidden = False
strPassword = InputBox(“Pls enter the password”)
ActiveSheet.Protect Password:=strPassword
End Sub

Watch the Excel training video below to see how you can protect your worksheet, lock specific cells and hide their formulas and allow editing of specific ranges with Excel VBA or a macro:


One thought on “Protect Worksheet Using Excel VBA

Leave a Reply

Your email address will not be published. Required fields are marked *