protecting, unprotecting workbook macro error

mike_ate_a_pie

Board Regular
Joined
Sep 25, 2009
Messages
69
i have this code found on the net for protecting/unprotecting my workbook, however i am unable to work out the password, i have tried Password Input and changing it to other passwords but cant figure out what i'm missing.

sure it must be simple but just cant see it,

any help appreciated

thanks


Option Explicit

Sub ProtectAll()

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
For Each wSheet In Worksheets
wSheet.Protect Password:=Pwd
Next wSheet

End Sub

Sub UnProtectAll()

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")
On Error Resume Next
For Each wSheet In Worksheets
wSheet.Unprotect Password:=Pwd
Next wSheet
If Err <> 0 Then
MsgBox "You have entered an incorect password. All worksheets could not " & _
"be unprotected.", vbCritical, "Incorect Password"
End If
On Error Goto 0

End Sub </pre>
 

mike_ate_a_pie

Board Regular
Joined
Sep 25, 2009
Messages
69
i havent ran the Protect ALl this was copied from another help website.

is there a alternative way to do this?

cheers
 

mike_ate_a_pie

Board Regular
Joined
Sep 25, 2009
Messages
69
I am trying to protect the entire workbook from being edited.

I would like to include one button to protect the workbook and another for unprotecting the workbook and allowing the user to make changes. With both buttons being password protected with the same password.

Hope this helps

thanks :)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
So assign ProtectAll and UnProtectAll to your buttons. Click the one for ProtectAll. Be sure to remember the password you entered. Then click the one for UnProtectAll and enter the password.
 

mike_ate_a_pie

Board Regular
Joined
Sep 25, 2009
Messages
69
cool, i've now got it to work , however is there any way to stop the unprotect macro scanning through every worksheet, even if it paused on the same page as this would appear a lot more professional.

thanks
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Application.ScreenUpdating=False before the loop, and change back to True after the loop.
 

mike_ate_a_pie

Board Regular
Joined
Sep 25, 2009
Messages
69
Hi again,

I just realsied that when i protect my sheet for some reason some macro buttons still work which is desired but others dont and come up as needing debugged! Is there any reason for this and how can I make the macro buttons work when the sheet is protected?

Thanks

Mike
 

Forum statistics

Threads
1,082,018
Messages
5,362,702
Members
400,686
Latest member
Aakash

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top