How to set a password for adding new sheets in a workbook using VBA?

To set a password for adding new sheets in a workbook using VBA, you can create a macro that intercepts the action of adding new sheets and prompts the user for a password. Here’s a step-by-step guide to implementing this functionality:

' Define the correct password
Const CorrectPassword As String = "YourPasswordHere"

' This function intercepts the worksheet add event
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim userPassword As String
    userPassword = InputBox("Enter password to add a new sheet:", "Password Required")
    
    If userPassword <> CorrectPassword Then
        Application.DisplayAlerts = False
        Sh.Delete
        Application.DisplayAlerts = True
        MsgBox "Incorrect password. The sheet was not added.", vbExclamation, "Access Denied"
    End If
End Sub

Open the VBA Editor:

Press `ALT` + `F11` in Excel to open the Visual Basic for Applications (VBA) editor.

Insert a Module:

In the VBA editor, right-click on any of the existing objects for your workbook in the “Project” window.
Select `Insert` > `Module` to add a new module.

Write the VBA Code:

Paste the following code into the new module. This code will prompt the user for a password whenever they try to add a new worksheet.

Modify the Password:

Replace `”YourPasswordHere”` with your desired password in the `Const CorrectPassword` line.

Install the Code in the Workbook:

Copy the entire code block.
In the VBA editor, find `ThisWorkbook` on the left under the Microsoft Excel Objects.
Double-click `ThisWorkbook` to open its code window, and paste the code there.

Save the Workbook:

Save your workbook as a macro-enabled file (`.xlsm`).

Enable Macros:

Ensure that your Excel settings are set to allow macros to run because this solution relies on macros.

Whenever a new sheet is added, this code will run, check the entered password, and either allow the addition or delete the new sheet if the password is incorrect. Remember, this approach uses an input box for password entry, which is not highly secure, as VBA does not provide a masked text box. It is advisable to use such measures in environments with low security concerns.

facebooklinkedin