Solved

ActiveSheet.Unprotect - if password fails in VBA

Posted on 2014-10-07
8
866 Views
Last Modified: 2014-11-18
Hello,

I have in my code the unprotecting of a worksheet (which is password protected). If the user enters the wrong password (or none at all) how can I capture this error and gracefully exit the macro? Is there an onerror or something that can be used here?

Regards,

GISVPN
0
Comment
Question by:gisvpn
  • 5
8 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40366816
Put this before

On error resume next


Then after the password VBA line put
On error go to 0
0
 

Author Comment

by:gisvpn
ID: 40366820
Hi ProfessorJimJam,

Thanks for the post - what does this do (would this exit the code) - Could I also add an MsgBox with a message on exit from the code?
0
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 500 total points
ID: 40366935
yes, it jumps to the error handler and subsequently ends the procedure.   see the example below


Private Sub test()
 On Error goto Message
 ActiveSheet.Unprotect
 Message:
 MsgBox("Wrong password entered")
 End Sub
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40366948
I am not sure, how your code looks like.  

but the simple code to prompt for password and if it is incorrect the gives the message box then the following code would be useful.

Sub UnProtectAllworksheets() 
     
    Dim wSheet          As Worksheet 
    Dim Pwd             As String 
     
    Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input") 
    On Error Resume Next 
    For Each wSheet In Worksheets 
        wSheet.Unprotect Password:=Pwd 
    Next wSheet 
    If Err <> 0 Then 
        MsgBox "You have entered an incorect password. All worksheets could not " & _ 
        "be unprotected.", vbCritical, "Incorect Password" 
    End If 
    On Error Goto 0 
     
End Sub 

Open in new window

0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40451012
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40451011
The author need to provide on the last comment/solution provided to him
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40451013
Author response required
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Outlook Free & Paid Tools
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now