VBA Code debug

aysam

Board Regular
Joined
Jun 24, 2009
Messages
217
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 :
Code:
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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

Board Regular
Joined
Jun 24, 2009
Messages
217
Dear HalfAce
Thanks so much for your help
But
How do I assign "my password" to _for example_ Range ("A1").Value
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456

ADVERTISEMENT

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

Board Regular
Joined
Jun 24, 2009
Messages
217
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

Board Regular
Joined
Jun 24, 2009
Messages
217

ADVERTISEMENT

awaiting !!!!!
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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.

Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,712
Messages
5,833,267
Members
430,200
Latest member
ADLHMA2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top