VBA Code debug

aysam

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

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

HalfAce

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

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
213
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
213

ADVERTISEMENT

awaiting !!!!!
 

HalfAce

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

Watch MrExcel Video

Forum statistics

Threads
1,108,452
Messages
5,523,068
Members
409,496
Latest member
SWH

This Week's Hot Topics

Top