Protection Macro

[Spheon]

I am completly ignorant and haven'y a clue about macro's and VBA...

Two things

I hope someone can help me with creating a password for all sheets at once
and then a second to 'unlock' them at once while keeping protection wherby
another cannot see the macro or itself has a password...

I know passwords can be cracked easily, someone has done it to the data I
was trying to protect! If I had an enormous random character password for the
protection...would this help? Are most crackers available limited to so many
character length etc?

please help....I now wish I had created the file in access!

many thanks in advance

Spheon

 

[Paul B]

Spheon, try this,

Sub Protect_All_Sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Protect password:="123"

Next ws

End Sub



Sub Unprotect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect password:="123"
Next ws
End Sub



To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in Project Explorer click on your workbook name, if you
don't see it press CTRL + r to open the Project Explorer, then go to insert,
module, and paste the code in the window that opens on the right hand side,
press Alt and Q to close this window and go back to your workbook and press
alt and F8, this will bring up a box to pick the Macro from, click on the
Macro name to run it. If you are using excel 2000 or newer you may have to
change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium


It does not matter how long the password is, it can be broken very easy

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Spheon]

thank you....it is so frustrating that we can add passwords yet they are so
easily broken...

how would i hide the macro/protect it for instance I could press ctrl+j and
it would run the unprotect routine etc...

thanks again

 

[Paul B]

Spheon, if you put private in front of the macro name you want see it in the
list, but you can not use a short cut key then, you could ask for a password
to run this macro, the password for this and the sheets can be different if
you want, something like this

put a button on the sheet and assign the macro to it

Sub Protect_All_Sheets()
Dim MyStr1 As String, MyStr2 As String
Dim ws As Worksheet

With ActiveSheet
MyStr2 = ("123") 'This is the password to run the macro
MyStr1 = InputBox("Password Is Required To Run this Macro ")
If MyStr1 = MyStr2 Then

For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="123" 'This is the password for each sheet
Next ws

Else
MsgBox ("Access Denied")
End If
End With
End Sub


You will also need to protect your VBA project so you can not see the
password(s) from there

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is to the left of the "File" menu this
will open the VBA editor, in Project Explorer right click on your workbook
name, if you don't see it press CTRL + r to open the Project Explorer then
select VBA project properties, protection, check lock project for viewing
and set a password. Press Alt and Q to close this window and go back to your
workbook and save and close the file. Be aware that this password can be
broken by third party software.

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Spheon]

excellent...many thanks!

Spheon, if you put private in front of the macro name you want see it in the
list, but you can not use a short cut key then, you could ask for a password
to run this macro, the password for this and the sheets can be different if
you want, something like this

put a button on the sheet and assign the macro to it

Sub Protect_All_Sheets()
Dim MyStr1 As String, MyStr2 As String
Dim ws As Worksheet

With ActiveSheet
MyStr2 = ("123") 'This is the password to run the macro
MyStr1 = InputBox("Password Is Required To Run this Macro ")
If MyStr1 = MyStr2 Then

For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="123" 'This is the password for each sheet
Next ws

Else
MsgBox ("Access Denied")
End If
End With
End Sub


You will also need to protect your VBA project so you can not see the
password(s) from there

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is to the left of the "File" menu this
will open the VBA editor, in Project Explorer right click on your workbook
name, if you don't see it press CTRL + r to open the Project Explorer then
select VBA project properties, protection, check lock project for viewing
and set a password. Press Alt and Q to close this window and go back to your
workbook and save and close the file. Be aware that this password can be
broken by third party software.

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Paul B]

Your welcome

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003