In this blog post, you will learn about sheet protection in
Excel VBA.
What is protecting and unprotecting Excel worksheets?
Protecting a worksheet is putting a password in a worksheet so that nobody can edit or modify the data.
Only an authorized person can unprotect the worksheets by putting the password to edit or modify.
When we are trying to put or enter the data into a protected worksheet, then Excel shows the below error pop-up with a message.
See in the below image:-
In Excel, we do sheet protection by right-clicking on the ”Sheet name” on the sheet tab bar at the bottom, which we want to protect.
And select the sheet protection option and put the password to protect the worksheet.
Here, you will learn about how to apply for worksheet protection in excel VBA.
We will apply the protect or unprotect sheet on sheet “Main” from the mentioned sheets on the bottom sheet tab bar.
Select sheet “Main” and go to the Developer tab and click on Visual Basic” tab and then “Insert” a “Module”.
Write the below code to protect and unprotect the worksheet
In this macro, we are using the password as "abc".
Sub
Sheet_Protect()
Sheets("Main").Protect
Password = "abc"
Sheets("Main").Unprotect
Password = "abc"
End Sub
Now execute the code, step by step by pressing F8 until End Sub.
When we execute our first line of code by pressing “F8”, the sheet is protected.
Now try to enter anything in the sheet “Main”, you will get the error message.
See image-01 for an error message.
Again by pressing “F8” from the keyboard, execute the second line of code
When
we execute the second line of code, our sheet protection has been removed and
our sheet is not protected now.
Now
we can enter the data easily in the worksheet without showing any error
message.
I
hope you find this tutorial useful.
Please
feel free to put your comments and suggestion in the below comment box.
Thank you.
No comments:
Post a Comment