VBA enter password to unhide worksheet

[colinheslop1984]

ThisWorkbook.Worksheets("jun payroll").Visible = xlSheetVisible

I am using the code above which unhides a worksheet when a button is clicked.

How do I set it so that a password is required to unhide it?

I am new to VBA so if you could write the code in your response that would be a huge help!

Thanks for help

 

[eduzs]

Try this:
(save close any other files you're using before testing macros, backup your original file before doing any modification, test in a throwaway copy of your file)

with ThisWorkbook.Worksheets("jun payroll")
    .Visible = xlSheetVisible
    .Protect [B][COLOR=red]Password:="YourPassword",[/COLOR][/B] DrawingObjects:=True, Contents:=True, Scenarios:=True _ 
    , AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End with

To unprotect via VBA:

ThisWorkbook.Worksheets("jun payroll").UnProtect [B][COLOR=red]Password:="YourPassword"[/COLOR][/B]

 

[mumps]

Assign this macro to your button. Change the password (in red) to suit your needs.

Sub unHideSheet()
    Dim PW As String, response As String
    PW = "[COLOR="#FF0000"]MyPassword[/COLOR]"
    response = InputBox("Please enter the password to unhide the sheet.")
    If response = PW Then
        Sheets("jun payroll").Visible = xlSheetVisible
    Else
        MsgBox ("Invalid password.")
    End If
End Sub

 

[Misca]

I might try something like:

Sub ToggleSheetVisibilityWithPassword()

Dim WS As Worksheet
Dim MyPassWord As String
Dim EnteredPassword As String


'The worksheet:
Set WS = Sheets(2)


'The real password:
MyPassWord = "PassWrd"


    If WS.Visible <> xlSheetVisible Then
    'Only ask password if the sheet is not visible:
        
        EnteredPassword = Application.InputBox(Prompt:="Enter the password:", Title:="Password needed", Type:=2)
        
        If EnteredPassword <> MyPassWord Then   'If Passwords don't match
            
            MsgBox "Try again.", vbOKOnly, "Wrong password!"
            Exit Sub
        
        End If
    
    End If
        
    'The code only runs this far if the sheet was visible or the password was entered correctly:
        
        ActiveWorkbook.Unprotect Password:=MyPassWord
        
        With WS
            .Visible = Not (.Visible)   'Toggles
        End With
            
        ActiveWorkbook.Protect Password:=MyPassWord, Structure:=True, Windows:=False


End Sub

What I don't like about the code is that you need to have the real password somewhere in the code. You could try to hide if from a casual user using ASCII values instead of normal text but it's a lot harder to set up than it is to crack unless you protect the VBA project as well so I wouldn't go that far.

You can get roughly the same protection without the passwords if you set the worksheet visibility to very hidden instead of hidden when you hide the worksheet:

Sub HideSheet2()

Dim WS As Worksheet


Set WS = Sheets(2)


With WS


    If .Visible = xlSheetVisible Then
        .Visible = xlSheetVeryHidden
    Else
        .Visible = xlSheetVisible
    End If


End With


End Sub

The very hidden sheets can only be seen in the VBA editor when not visible.