Protect Specific Excel Worksheets Using VBA

Protecting Specific Worksheets in an Excel Workbook using VBA
Microsoft VBA question
I have 5 worksheets in my workbook. I wish to password protect the sheets four and five when the workbook is opened. That is, the sheets 4 and 5 are
not visible to somebody who doesn’t have a password. How can I implement this? Thanks for your help in advance!
Andrea from Goa wants to do the following:
•Create a user-form that loads when the workbook is opened
•The user is required to enter a password in a textbox
•The password should not be visible during entry (obviously!)

•On clicking on the login command button the password is authenticated and the user can see all the worksheets
•In case the password entered is incorrect the user has an opportunity to retry.
•If she doesn’t want to retry or doesn’t have a password, she can select ‘no’ and proceed to open the workbook without the protected worksheets.
How to implement the process step by step:

1.Click on the developer tab
2.Select ‘Visual Basic’
3.Double-click on the item ‘this workbook’ on the left-side of the window
4.Enter the code below by selecting ‘workbook’ in the right-hand side window under ‘General’ and under declarations select ‘Open’. Also select ‘workbook’ under ‘General’ and ‘Before close’ under ‘Declarations’ and write the relevant code as shown below.

VBA coding when the user form loads
Private Sub Workbook_Open()
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets(“Sheet4”).Visible = False
Sheets(“Sheet5”).Visible = False
End Sub

Next click on the ‘insert’ menu, select ‘userform’ and on the form create your controls using standard methods to provide a label (enter password), a textbox and a command button (login). Then write code for the command button as shown below:

VBA code for the command button:
Private Sub CommandButton1_Click()
If Me.TextBox1.Value = “azby,,1029” Then (How to hide this VBA code – video)
Unload Me
Sheets(“Sheet4”).Visible = True
Sheets(“Sheet5”).Visible = True
Retry = MsgBox(“The password is incorrect. Do you wish to try again?”, vbYesNo, “Retry?”)
Select Case Retry
Case Is = vbYes
Me.TextBox1.Value = “”
Case Is = vbNo
Unload Me
End Select
End If
End Sub

Watch the video below to see how the complete process can be implemented easily:

4 thoughts on “Protect Specific Excel Worksheets Using VBA”

  1. first of all thanks for great tutorials, I have a work book with 7 work sheets, 1st work sheet named home and its include links to another sheets, I need to make separate passwords for 6 sheets (Except Home) as it is using 6 different users.. Kindly help

  2. Thanks for the great tutorial. I have followed all the steps but when I click on unhide bottom am able to unhide the hidden sheets and work on them. Is there a way to avoid this?

  3. Thanks for the tutorial. It worked fine for me, but I have the same concern of Mr. John Kuria about the sheets, since they still able to be unhide. I would like to know if there is a way to protect them. I have tried through the properties window by change the sheet from hidden to very hidden, but it did not work.
    Thanks in advance for your reply.

  4. Thank you for the help. I have followed specified steps and it is working fine but when I am using these sheets through google sheets these changes are not at all reflecting there. Please guide me as how to persist those changes in google sheet as well.
    Thanking you in anticipation.
    Connect me through

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.