Command Button that shows Hidden Sheets - Needs Password Protection

Hello All,

I have a Command Button that shows all hidden sheets in the workbook... Here is the code

Sub ShowDataSheets()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End Sub

I am needing to code the macro so that the user must enter a specific password in order to use the Command Button.

Any solutions in mind!


Sorry about missing the Code Tags!


Best Regards

John

Hi, JJFletcher

please have a look at Forum Rule#3 to understand why you are asked to apply code-tags to your procedure.

Ciao,
Holger

Hi, John,

please try

Sub ShowDataSheets()

Dim ws As Worksheet
Const cstrPW As String = "strangePW"

If InputBox(Prompt:="Enter the password", Title:="Enter Password", Default:="enter password here") = cstrPW Then
  For Each ws In ThisWorkbook.Worksheets
    ws.Visible = xlSheetVisible
  Next ws
Else
  MsgBox "Sorry, wrong password", vbInformation, "No luck here"
'  ThisWorkbook.Close True
End If

End Sub

Thanks for adding the code-tags.

Ciao,
Holger

Hi, John,

thanks for the feedback.

A small alteration for the number of tries which might be of interest to you as well:

Sub ShowDataSheets()

Dim ws As Worksheet
Static lngTries As Long

Const cstrPW As String = "strangePW"
Const clngMAX As Long = 3

If InputBox(Prompt:="Enter the password", Title:="Enter password", Default:="enter password here") = cstrPW Then
  For Each ws In ThisWorkbook.Worksheets
    ws.Visible = xlSheetVisible
  Next ws
Else
  lngTries = lngTries + 1
  MsgBox "Sorry, wrong password", vbInformation, "Missed trie: " & lngTries & " of " & clngMAX & " tries"
  If lngTries = clngMAX Then ThisWorkbook.Close True
End If

End Sub

Ciao,
Holger

Thanks so much... I saw somewhere that I can specify to only open specific sheets desired - not all sheets in the Workbook, but ones that only have "Data" as part of it's description...

Can the code below also be coded to only allow the sheets with "Data" be shown?

Sub ShowDataSheets()

Dim ws As Worksheet
Static lngTries As Long

Const cstrPW As String = "strangePW"
Const clngMAX As Long = 3

If InputBox(Prompt:="Enter the password", Title:="Enter password", Default:="enter password here") = cstrPW Then
  For Each ws In ThisWorkbook.Worksheets
    ws.Visible = xlSheetVisible
  Next ws
Else
  lngTries = lngTries + 1
  MsgBox "Sorry, wrong password", vbInformation, "Missed trie: " & lngTries & " of " & clngMAX & " tries"
  If lngTries = clngMAX Then ThisWorkbook.Close True
End If

End Sub

John

Ciao,
Holger... Absolutely Superb... Works just as I want it to... Thanks So Much ... Really

Hi, John,

if you want to use the InputBox the only way I know is to use API to mask the characters being entered. I would recommend you have a look at UserForms where you may add a Textbox (PasswordChar can be given there) which would mask the entry without further notice. A simple UserForm could consist of one label for some explanation what to do, one textbox for the entry, one commandbutton for cancelling and one for submit with the code to check like given above (and the command to unload the userform)

Ciao,
Holger