Hi Tom

Hope all is good in your world.

I wanted to see if you know if there is a way to let the client choose the password to open the page to the following code.  the options compare text kinda has me a bit confused.  The code you sent me is great and works fine, just wondering if there is a way, if a client were to type into a cell from say worksheet "password" that this code could take that specific password in a specific example

worksheet "password" cell a1 he inputs hello
and in A2 he inputs goodbye.....

can u do a range or specific cell? and have that code below when executed save that info as the password, so if they ever want to change it they can do it instead of me having to do so by recoding....


Option Compare Text
'Password to unhide sheets
Const pWord = "noah2000"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True

   If Not Intersect(Target, Range("E23")) Is Nothing Then
   Select Case InputBox("Please enter the password to unhide the sheet", _
       "Enter Password")
       Case Is = pWord
         With Worksheets("0")
         .Visible = xlSheetVisible
   ActiveWindow.SelectedSheets.Visible = False
   ActiveSheet.Unprotect Password:="DAK"
   ActiveWindow.DisplayHeadings = False
     On Error Resume Next
   Selection.EntireRow.Hidden = False
         End With
       Case Else
         MsgBox "Sorry, that password is incorrect!", _
         vbCritical + vbOKOnly, "You are not authorized!"
   End Select
    Application.ScreenUpdating = True
   End If
If Not Intersect(Target, Range("AC2")) Is Nothing Then
   Application.ScreenUpdating = False

For Each sh In Worksheets
If InStr(1, sh.Name, "index", vbTextCompare) > 0 Then
   sh.Visible = xlVeryHidden
   Application.ScreenUpdating = True
End If
   End If
End Sub

Thanks Tom


If you wanted to provide the user the ability to change the password, you would remove the constant declaration from your code.  then you would add code that gets the password from some location.  That could be on a hidden sheet or in a defined name or some other place where the password can be retrieved from.  So for the user to change that password, you would have to add code to prompt the user for the new password and then replace the existing password in this location where the password is stored.  

After changing the password, you would need to save the file so that the changes made would be retained by the file.

Option Compare Text just makes any text comparison as case INSENSITIVE.


"password" = "PASSWORD"   would be true.

when I run this code

Sub test()
 v = Array("Password", "password", "passWORD", "Dog", "PASSword")
 pword = "PaSsWoRd"
 For i = LBound(v) To UBound(v)
   Select Case v(i)
     Case Is = pword
       Debug.Print i & ". Match: " & v(i) & " = " & pword
     Case Else
       Debug.Print i & ". Match: " & v(i) & " <> " & pword
   End Select
End Sub

with Option Compare Text, I get this result
1. Match: password = PaSsWoRd
2. Match: passWORD = PaSsWoRd
3. Match: Dog <> PaSsWoRd
4. Match: PASSword = PaSsWoRd

So Dog is the only password that does not match.

If I change to Option Compare Binary, I get this result:

1. Match: password <> PaSsWoRd
2. Match: passWORD <> PaSsWoRd
3. Match: Dog <> PaSsWoRd
4. Match: PASSword <> PaSsWoRd

none of the words I furnished as the password match.  

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 About.com. All rights reserved.