error with MsgBox

Hi Friends,

I worte a procedure to protect the worksheet. When we clik on command button it asks the password but if I click on cancel button procedure is exiting.

Please help me with this code.

Private Sub CommandButton1_Click()
Dim pword As String
Dim msg As String
Dim ans As Integer

TryAgain:
    ' Set up error handling if the entered password is wrong then control goes to the BadEntry label.
    On Error GoTo BadEntry
    'Assigned inputbox function to pword variable
    pword = InputBox("Enter the password", "Unlock the source sheet")
       
    'checks for password to unprotect the sheet
    If pword = "sadc" Then
        Sheets("source").Unprotect pword
    Exit Sub
    End If

BadEntry:
    'Assigned msgbox information to msg variable
    msg = "Wrong Password, Try again?"
    'Assigned msgbox information to ans variable
    ans = MsgBox(msg, vbYesNo, "Information")
    If ans = vbNo Then
       Exit Sub
    End If
    'It checks weather user selected yes or no command butoons if the user selected "Yes"
     'then the control goes to AryAgrain label.
    If ans = vbYes Then GoTo TryAgain
        
     'Then Resume TryAgain
'Exit Sub
End Sub

Thans and Regards
Ramesh

I think this is what you want

Option Explicit

Private Sub CommandButton1_Click()
    Dim pword  As String
    Dim msg    As String
    Dim ans    As Integer

TryAgain:
    ' Set up error handling if the entered password is wrong then control goes to the BadEntry label.
    On Error GoTo BadEntry
    'Assigned inputbox function to pword variable
    pword = InputBox("Enter the password", "Unlock the source sheet")

    'checks for password to unprotect the sheet
    Select Case pword
        Case "sadc"
            Sheets("source").Unprotect pword
            Exit Sub
        Case "": GoTo TryAgain
       ' Case Else: GoTo BadEntry
    End Select

BadEntry:
    'Assigned msgbox information to msg variable
    msg = "Wrong Password, Try again?"
    'Assigned msgbox information to ans variable
    ans = MsgBox(msg, vbYesNo, "Information")
    If ans = vbNo Then
        Exit Sub
    End If
    'It checks weather user selected yes or no command butoons if the user selected "Yes"
    'then the control goes to AryAgrain label.
    If ans = vbYes Then GoTo TryAgain

    'Then Resume TryAgain
    'Exit Sub
End Sub

The password for the attched sheet is not sadc

I've changed the password to sadc & the code works ine

Option Explicit

Private Sub CommandButton1_Click()
    Dim pword  As String
    Dim msg    As String
    Dim ans    As Integer

TryAgain:
    ' Set up error handling if the entered password is wrong then control goes to the BadEntry label.
    On Error GoTo BadEntry
    'Assigned inputbox function to pword variable
    pword = InputBox("Enter the password", "Unlock the source sheet")

    'checks for password to unprotect the sheet
    Select Case pword
        Case "sadc"
            Sheets("source").Unprotect pword
            Exit Sub
        Case "": GoTo TryAgain
        Case Else: GoTo BadEntry
    End Select

BadEntry:
    'Assigned msgbox information to msg variable
    msg = "Wrong Password, Try again?"
    'Assigned msgbox information to ans variable
    ans = MsgBox(msg, vbYesNo, "Information")
    If ans = vbNo Then
        Exit Sub
    End If
    'It checks weather user selected yes or no command butoons if the user selected "Yes"
    'then the control goes to AryAgrain label.
    If ans = vbYes Then GoTo TryAgain

    'Then Resume TryAgain
    'Exit Sub
End Sub

Your code was written that way, all I've done is make it work as the code seemed to want it to. Have you edited someone else's code?

The idea with the Forum is to provide help, you should study the code & try to make such simple changes yourself

Option Explicit

Private Sub CommandButton1_Click()
    Dim pword  As String
    Dim msg    As String
    Dim ans    As Integer

TryAgain:
    ' Set up error handling if the entered password is wrong then control goes to the BadEntry label.
    On Error GoTo BadEntry
    'Assigned inputbox function to pword variable
    pword = InputBox("Enter the password", "Unlock the source sheet")

    'checks for password to unprotect the sheet
    Select Case pword
        Case "sadc"
            Sheets("source").Unprotect pword
            Exit Sub
        Case "": Exit Sub
        Case Else: GoTo BadEntry
    End Select

BadEntry:
    'Assigned msgbox information to msg variable
    msg = "Wrong Password, Try again?"
    'Assigned msgbox information to ans variable
    ans = MsgBox(msg, vbYesNo, "Information")
    If ans = vbNo Then
        Exit Sub
    End If
    'It checks weather user selected yes or no command butoons if the user selected "Yes"
    'then the control goes to AryAgrain label.
    If ans = vbYes Then GoTo TryAgain

    'Then Resume TryAgain
    'Exit Sub
End Sub

No problem. try to understand the code 7 post back with specific questions on anyhing that you do not understandIf you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You might also like to rate any answer(s)