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:

 


Protect Worksheet

2 thoughts on “Protect Worksheet Using Excel VBA

  1. Hi Sir,
    First of all, I want to say a big thank you for your effort and time in sharing your knowledge to some of the less forfunate people like me who have the desire to advance myself via this platform. I am Liberian who live in french-speaking Togo (West Africa). I fled the 14yrs old Civil War that ravaged my country. I do Computer Graphics, using Coreldraw and Photoshop and I have some knowledge in the usage of most Office programs. From the way you explain the VBA section, which is my interest of focus, I haave no brought knowledge but would like to learn coding. So, please can you help me learn the basic in Coding. Thanks in advance and look forward to hearing from you.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.