Add message box when wrong info entered

[KatKitKat]

Hi.. I am getting tied up on something. I have a protected worksheet that only allows data in to be added to certain cells. I want to add a macro button to unprotect the entire sheet that required the user to enter the correct password. That is working, but I want to add a pop up message box if an incorrect password is added. And this is not working. My code is below if anyone can see where my error is. Should this could be in a Module or the Worksheet page?

Thanks!

Sub Button6_Click() 'unprotect Contingency Sheet



Dim ws As Worksheet

Set ws = Sheets("Contingency Use Report")



Dim Pwrd As Variant

Dim Proceed As Boolean



Proceed = False

Do

Pwrd = Application.InputBox("Please enter password")

If Pwrd = False Then Exit Sub

If Pwrd = "1234" Then

Proceed = True

Else

MsgBox "Incorrect Password Entered. Please try again!", vbOKOnly

End If



Loop Until Proceed

End Sub

 

[dmt32]

Hi,
try following & see if will do what you want

Place both codes in a STANDARD module

Sub Button6_Click()
'unprotect Contingency Sheet
    Dim ws As Worksheet
  
    Const Pwrd As String = "1234"
  
    Dim Proceed As Boolean
  
    Set ws = ThisWorkbook.Worksheets("Contingency Use Report")
  
    ws.Protect Pwrd
  
    If IsValidPassword(Pwrd, True) Then ws.Unprotect Pwrd Else Exit Sub
  
    'do other stuff if needed & valid password

End Sub


Function IsValidPassword(ByVal Password As String, Optional ByVal Success As Boolean) As Boolean
    Dim Entry   As Variant
    Dim Prompt  As String, Title As String
    Dim try     As Long
  
    try = 1
    Title = "Enter Password"
    Do
        Prompt = "Please Enter Password To Continue"
        Prompt = Prompt & Chr(10) & Chr(10) & _
                  "attempt " & try & " of 3"

        Entry = InputBox(Prompt, Title)
        'cancel pressed
        If StrPtr(Entry) = 0 Then Exit Do
      
        If Entry <> Password And try = 3 Then
                MsgBox "Three Attempts Only Allowed", 16, "Three Attempts Only"
                Exit Do
        ElseIf Entry <> Password Then
                MsgBox "Password invalid - Please try again", 48, "Invalid Password"
                try = try + 1
        Else
         If Success Then MsgBox "Password Correct - Click OK To Continue.", 48, "Password Correct"
                Exit Do
        End If
    Loop
    IsValidPassword = Entry = Password
End Function

As it looks like you are using a Forms button, you should have already assigned the Macro to "Button6"

The Function has two parameters
- Password (required) - the required sheet password
- Success (optional) - Informs user if password entered is successful. Set False (or just omit) if not required.

The function allows you to set passwords on other sheets in your project if required

Hope Helpful

Dave

 

[KatKitKat]

Hi,
try following & see if will do what you want

Place both codes in a STANDARD module

Sub Button6_Click()
'unprotect Contingency Sheet
    Dim ws As Worksheet
 
    Const Pwrd As String = "1234"
 
    Dim Proceed As Boolean
 
    Set ws = ThisWorkbook.Worksheets("Contingency Use Report")
 
    ws.Protect Pwrd
 
    If IsValidPassword(Pwrd, True) Then ws.Unprotect Pwrd Else Exit Sub
 
    'do other stuff if needed & valid password

End Sub


Function IsValidPassword(ByVal Password As String, Optional ByVal Success As Boolean) As Boolean
    Dim Entry   As Variant
    Dim Prompt  As String, Title As String
    Dim try     As Long
 
    try = 1
    Title = "Enter Password"
    Do
        Prompt = "Please Enter Password To Continue"
        Prompt = Prompt & Chr(10) & Chr(10) & _
                  "attempt " & try & " of 3"

        Entry = InputBox(Prompt, Title)
        'cancel pressed
        If StrPtr(Entry) = 0 Then Exit Do
     
        If Entry <> Password And try = 3 Then
                MsgBox "Three Attempts Only Allowed", 16, "Three Attempts Only"
                Exit Do
        ElseIf Entry <> Password Then
                MsgBox "Password invalid - Please try again", 48, "Invalid Password"
                try = try + 1
        Else
         If Success Then MsgBox "Password Correct - Click OK To Continue.", 48, "Password Correct"
                Exit Do
        End If
    Loop
    IsValidPassword = Entry = Password
End Function

As it looks like you are using a Forms button, you should have already assigned the Macro to "Button6"

The Function has two parameters
- Password (required) - the required sheet password
- Success (optional) - Informs user if password entered is successful. Set False (or just omit) if not required.

The function allows you to set passwords on other sheets in your project if required

Hope Helpful

Dave

Thanks. I will try and let you know how it goes.
Kat

 

[KatKitKat]

Hi,
try following & see if will do what you want

Place both codes in a STANDARD module

Sub Button6_Click()
'unprotect Contingency Sheet
    Dim ws As Worksheet
 
    Const Pwrd As String = "1234"
 
    Dim Proceed As Boolean
 
    Set ws = ThisWorkbook.Worksheets("Contingency Use Report")
 
    ws.Protect Pwrd
 
    If IsValidPassword(Pwrd, True) Then ws.Unprotect Pwrd Else Exit Sub
 
    'do other stuff if needed & valid password

End Sub


Function IsValidPassword(ByVal Password As String, Optional ByVal Success As Boolean) As Boolean
    Dim Entry   As Variant
    Dim Prompt  As String, Title As String
    Dim try     As Long
 
    try = 1
    Title = "Enter Password"
    Do
        Prompt = "Please Enter Password To Continue"
        Prompt = Prompt & Chr(10) & Chr(10) & _
                  "attempt " & try & " of 3"

        Entry = InputBox(Prompt, Title)
        'cancel pressed
        If StrPtr(Entry) = 0 Then Exit Do
     
        If Entry <> Password And try = 3 Then
                MsgBox "Three Attempts Only Allowed", 16, "Three Attempts Only"
                Exit Do
        ElseIf Entry <> Password Then
                MsgBox "Password invalid - Please try again", 48, "Invalid Password"
                try = try + 1
        Else
         If Success Then MsgBox "Password Correct - Click OK To Continue.", 48, "Password Correct"
                Exit Do
        End If
    Loop
    IsValidPassword = Entry = Password
End Function

As it looks like you are using a Forms button, you should have already assigned the Macro to "Button6"

The Function has two parameters
- Password (required) - the required sheet password
- Success (optional) - Informs user if password entered is successful. Set False (or just omit) if not required.

The function allows you to set passwords on other sheets in your project if required

Hope Helpful

Dave

Hi Dave,

I did this and it loops through doing what it should except I get a Run time error 1004 that the password supplied is not correct... When I debug, the "ws.Unprotect Pwrd Else" in the Sub Button6 code is highlighted and the sheet doesn't unlock. Any ideas what I did wrong? I do appreciate your help with this.

Thanks - Kat

 

[dmt32]

The password you are using does not match the password you already applied to the sheet

Const Pwrd As String = "1234"

Updated code will trap such an error

Sub Button6_Click()
'unprotect Contingency Sheet
    Dim ws As Worksheet
    
    Const Pwrd As String = "1234"
    
    Dim Proceed As Boolean
    
    On Error GoTo myerror
    Set ws = ThisWorkbook.Worksheets("Contingency Use Report")
    
    Proceed = IsValidPassword(Pwrd, True)
    
    ws.Protect Pwrd
    
    If Proceed Then ws.Unprotect Pwrd Else Exit Sub
    
    'do other stuff if needed & valid password
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

the Success message if you are using it, will conflict (reports success if match but errors when tries to unprotect sheet if password invalid)
Suggest set the argument to false & I will when have moment, have a look at updating

Proceed = IsValidPassword(Pwrd, False)

Dave

 

[KatKitKat]

Hi,
try following & see if will do what you want

Place both codes in a STANDARD module

Sub Button6_Click()
'unprotect Contingency Sheet
    Dim ws As Worksheet
 
    Const Pwrd As String = "1234"
 
    Dim Proceed As Boolean
 
    Set ws = ThisWorkbook.Worksheets("Contingency Use Report")
 
    ws.Protect Pwrd
 
    If IsValidPassword(Pwrd, True) Then ws.Unprotect Pwrd Else Exit Sub
 
    'do other stuff if needed & valid password

End Sub


Function IsValidPassword(ByVal Password As String, Optional ByVal Success As Boolean) As Boolean
    Dim Entry   As Variant
    Dim Prompt  As String, Title As String
    Dim try     As Long
 
    try = 1
    Title = "Enter Password"
    Do
        Prompt = "Please Enter Password To Continue"
        Prompt = Prompt & Chr(10) & Chr(10) & _
                  "attempt " & try & " of 3"

        Entry = InputBox(Prompt, Title)
        'cancel pressed
        If StrPtr(Entry) = 0 Then Exit Do
     
        If Entry <> Password And try = 3 Then
                MsgBox "Three Attempts Only Allowed", 16, "Three Attempts Only"
                Exit Do
        ElseIf Entry <> Password Then
                MsgBox "Password invalid - Please try again", 48, "Invalid Password"
                try = try + 1
        Else
         If Success Then MsgBox "Password Correct - Click OK To Continue.", 48, "Password Correct"
                Exit Do
        End If
    Loop
    IsValidPassword = Entry = Password
End Function

As it looks like you are using a Forms button, you should have already assigned the Macro to "Button6"

The Function has two parameters
- Password (required) - the required sheet password
- Success (optional) - Informs user if password entered is successful. Set False (or just omit) if not required.

The function allows you to set passwords on other sheets in your project if required

Hope Helpful

Dave

Got it. This worked. Thanks so much again, Dave. Enjoy your day!

 

[dmt32]

Got it. This worked. Thanks so much again, Dave. Enjoy your day!

sorry, I am out with grandchildren this weekend but glad you manged to resolve

Dave