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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,195
Messages
5,509,743
Members
408,752
Latest member
KrisF

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