How to code in VBA for requesting password when clicked on a button?

Question

I have got a command button on one of the excel sheet to hide/unhide one of the worksheet. See below code.

However, now I want to add a capability for the admin to enter the password in order to unhide the sheet, else anyone can click the button and unhide the data sheet.

Is there a way to force excel to show the normal enter password prompt to unhide?

Thanks

Sub myButton()

ActiveWorkbook.Unprotect

If (Sheets("Sheet2").Visible) Then 
    Sheets("Sheet2").Visible = False 
    Sheets("Sheet1").Select          
Else
    Sheets("Sheet2").Visible = True  
    Sheets("Sheet2").Select          
End If

ActiveWorkbook.Protect Password:="password", structure:=True, Windows:=False

End Sub

score 0 · Answer 1 · answered Aug 28 '19 at 23:28

0

You can use an inputbox to collect input from a user. Not exactly sure what you are trying to do, but maybe something like this works.

Option Explicit
Sub myButton()

    Dim pword As String

    pword = InputBox("Please enter a password", "Password Entry")

    If (Len(Trim$(pword)) = 0) Then Exit Sub

    ActiveWorkbook.Unprotect pword

    If (Sheets("Sheet2").Visible) Then
        Sheets("Sheet2").Visible = False
        Sheets("Sheet1").Select
    Else
        Sheets("Sheet2").Visible = True
        Sheets("Sheet2").Select
    End If

    ActiveWorkbook.Protect Password:=pword, structure:=True, Windows:=False

End Sub

answered Aug 28 '19 at 23:28

Ryan Wildry

5,612
1
15
35

This code works partially for me. Now I have to add password to both hide and unhide the worksheet. However, I don't want the user to enter the password to hide it i.e. when the click the button sheet 2 should just hide if its visible. – rikin bhavsar Aug 28 '19 at 23:36
Oh I just figured that even if you add a different password/string instead of "pword" then it will accept and unhide the worksheet. – rikin bhavsar Aug 29 '19 at 15:04

score 0 · Answer 2 · answered Aug 29 '19 at 15:41

I was able to resolve this with few tweaks. Thanks Ryan for your help!

Sub myButton()

Dim pword As String

If (Sheets("Sheet2").Visible) Then
    Sheets("Sheet2").Visible = False
    Sheets("Sheet1").Select
Else

pword = InputBox("Please enter a password", "Password Entry")

If pword = "password" Then

ActiveWorkbook.Unprotect "password"

    Sheets("Sheet2").Visible = True
    Sheets("Sheet2").Select

ActiveWorkbook.Protect Password:="password", structure:=True, Windows:=False

Else
    MsgBox ("Wrong Password")
End If
End If

End Sub

How to code in VBA for requesting password when clicked on a button?

2 Answers2