Store correct password

[schumanp]

Hi,

I am prompting users to enter a password when they click a button that runs a macro hiding and formatting cells.

Dim Password As String
Password = InputBox("Please enter password below", "Password", "????")
If Password <> "2154" Then
MsgBox "Incorrect Password"
Exit Sub
Else
End If



They need to enter the password every time they hit the button. Does anyone know how to store the password so that they only need to enter once while the sheet is open.

Thanks in advance for your help.

Nick

 

[Yongle]

Declare this boolean variable at top of a standard module (above all procedures)

Public OK As Boolean

and amend your code like this

Sub PasswordEntry()
    Dim Password As String
    [COLOR=#ff0000]If OK = True Then Exit Sub[/COLOR]
    Password = InputBox("Please enter password below", "Password", "????")
    If Password <> "2154" Then
        MsgBox "Incorrect Password"
        Exit Sub
    Else
        [COLOR=#ff0000]OK = True[/COLOR]
    End If
End Sub

 

[schumanp]

Many thanks! Works like a charm.

 

[schumanp]

Is it possible to have it run the rest of the sub? Now it exits the sub before fixing the rows and columns.

Public OK As Boolean


Sub RR_adjustments()
   
    
    Dim Password As String
    If OK = True Then Exit Sub
    Password = InputBox("Please enter password below", "Password", "????")
    If Password <> "2154" Then
        MsgBox "Incorrect Password"
        Exit Sub
    Else
        OK = True
    End If
   
    Sheets("RR").Unprotect ("2154")
    'Worksheets.("IR").Range("R186").Value="Password"
   
   Dim r As Range
   
   For Each r In Range("AI8:AI100").Cells
        If r.Value = "1" Then
            r.EntireRow.Hidden = True
            
        End If
    Next r
            
    For Each r In Range("AI8:AI100").Cells
        If r.Value = "0" Then
            r.EntireRow.Hidden = False


        End If
    Next r
   
   Dim c As Range


    For Each c In Range("M101:AF101").Cells
        If c.Value = "1" Then
            c.EntireColumn.Hidden = True


        End If
    Next c
    
    For Each c In Range("M101:AF101").Cells
        If c.Value = "0" Then
            c.EntireColumn.Hidden = False
            
        End If
    Next c




   
End Sub

 

[Fluff]

How about

Sub RR_adjustments()
   Static Pwrd As String
   Dim r As Range

   If Len(Pwrd) = 0 Then
      Pwrd = InputBox("Please enter password below", "Password", "????")
      If Pwrd <> "2154" Then
         MsgBox "Incorrect Password"
         Pwrd = ""
         Exit Sub
      End If
   End If
   Sheets("RR").Unprotect "2154"
   
   For Each r In Range("AI8:AI100").Cells
      If r.Value = "1" Then
         r.EntireRow.Hidden = True
      ElseIf r.Value = "0" Then
         r.EntireRow.Hidden = True
      End If
   Next r
   For Each r In Range("M101:AF101").Cells
      If r.Value = "1" Then
         r.EntireColumn.Hidden = True
      ElseIf r.Value = "0" Then
         r.EntireColumn.Hidden = False
      End If
   Next r
   Sheets("RR").Protect "2154"
End Sub

Do your 2 ranges contain anything other than 1 or 0?

 

[schumanp]

Thank you. This looks like it works. The ranges do not contain anything other than 1 or 0. They are the product of if statements based on if other cells are populated.

Thanks,

Nick

 

[Fluff]

In that case the 2 loops can be written like

   For Each r In Range("AI8:AI100").Cells
      r.EntireRow.Hidden = r.Value = "1"
   Next r
   For Each r In Range("M101:AF101").Cells
      r.EntireColumn.Hidden = r.Value = "1"
   Next r

 

[schumanp]

Thank you. I really appreciate the help.

 

[Fluff]

You're welcome