Password Protection Problem

joeeoj

New Member
Joined
Jun 17, 2010
Messages
19
Hi, recently I needed a way to unprotect all worksheets at once. I was given the idea to create a userform with the following code:

Code:
Private Sub CommandButton1_Click()
    'unprotect worksheets
 
    For Each Worksheet In Worksheets
 
    If Worksheet.ProtectContents = True Then
    Worksheet.Unprotect Password:=TextBox1.Text
    End If
 
    Next Worksheet
 
    MsgBox "Workbook will be re-protected upon exit."
 
    Unload Me
End Sub
This works great, but if the password entered is wrong, a Run-time error '1004' occurs. Is there any way that a message saying "Incorrect Password" comes up instead of this error?

Thanks!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
You could try something like this:

Code:
Private Sub CommandButton1_Click()
    'unprotect worksheets
 
    [B][COLOR=red]On Error GoTo ErrHandler[/COLOR][/B]
    For Each Worksheet In Worksheets
 
    If Worksheet.ProtectContents = True Then
    Worksheet.Unprotect Password:=TextBox1.Text
    End If
 
    Next Worksheet
 
    MsgBox "Workbook will be re-protected upon exit."
 
    Unload Me
[COLOR=red][B]ErrHandler:
    If Err.Number = 1004 Then
        MsgBox "Incorrect Password"
    End If[/B][/COLOR]
End Sub
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,502
Messages
5,511,693
Members
408,859
Latest member
willm57

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top