VBA Code debug

[aysam]

Peace to you all
I have got this code
The code is to password protect and unprotect a workbook in one go.
When I enter a wrong password it gives an error message
I want it to show a vba message " Wrong Password " and goes on.
Thanks in advance
the code is :

Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        If wSheet.ProtectContents = True Then
           wSheet.Unprotect Password:=TextBox1.Text
        Else
            wSheet.Protect Password:=TextBox1.Text
        End If
    Next wSheet
Unload Me
End Sub

 

[HalfAce]

Re: VBA Code debuge

One quick way would be to test the value of textbox1 before trying to go any further.
Try inserting this line above your For Each statement
If Me.TextBox1 <> "MyPassword" Then MsgBox "Invalid Password": Exit Sub

That help?

 

[aysam]

Dear HalfAce
Thanks so much for your help
But
How do I assign "my password" to _for example_ Range ("A1").Value

 

[aysam]

up
up

 

[HalfAce]

I'm not sure what you mean by assigning the password to A1.
Do you mean you want to enter the password in A1 and then compare the textbox to that? - Or use A1's value for the password the macro is looking for?
Or you want the password to get entered onto A1 by the macro?
Or...(?)

 

[aysam]

Sorry for being unclear
All What I want is to put the password in A1 to be able to change it without logging into the code
the file is to be distributed & each person has to put his own password by changing A1

 

[aysam]

awaiting !!!!!

 

[HalfAce]

If you mean to unprotect every sheet with the same password - being whatever gets typed into Sheet1 cell A1, then this should do it.
That seems like a very unstable way to go as any sheet that gets unprotected individually can easily screw up the works (and if anyone besides you will be using the workbook it's a sure thing sooner or later it'll happen) so I threw in a little error trapping that may help when the problem arises.

Private Sub CommandButton1_Click()
Dim wSheet As Worksheet, ShtErr As String
ShtErr = ""
Application.ScreenUpdating = False
For Each wSheet In Worksheets
    On Error Resume Next
    If wSheet.ProtectContents = True Then
       wSheet.Unprotect Password:=Sheets("Sheet1").Range("A1").Value
    Else
        wSheet.Protect Password:=Sheets("Sheet1").Range("A1").Value
    End If
    If Err.Number > 0 Then If Len(ShtErr) = 0 Then ShtErr = wSheet.Name Else ShtErr = ShtErr & ", " & wSheet.Name
    On Error GoTo 0
Next wSheet
Application.ScreenUpdating = True
If Len(ShtErr) > 0 Then MsgBox "Invalid password for the following sheet(s):" & vbCr & ShtErr
Unload Me
End Sub

Hope it helps.

 

[aysam]

thanks so much
I should try