Store correct password

schumanp

New Member
Joined
Jun 28, 2018
Messages
5
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Declare this boolean variable at top of a standard module (above all procedures)

Code:
Public OK As Boolean

and amend your code like this

Code:
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
 
Last edited:
Upvote 0
Is it possible to have it run the rest of the sub? Now it exits the sub before fixing the rows and columns.

Code:
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
 
Last edited by a moderator:
Upvote 0
How about
Code:
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?
 
Upvote 0
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
 
Upvote 0
In that case the 2 loops can be written like
Code:
   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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top