Solved MACRO to protect and Unprotect

Microsoft Office 2007 professional (aca...
June 10, 2010 at 04:59:57
Specs: Excel 2007
Hello every one

I need help in the below issue

I need 2 macros
1st
To protect all worksheets with the password and to protect workbook structure with the same password
2st
To Unprotect worksheets and workbook with the password in the protect macro

Thanks in advance


See More: MACRO to protect and Unprotect

Report •

#1
June 10, 2010 at 06:29:24
✔ Best Answer
Here you go...

Don't forget to set the VBA project properties to "Lock Project for Viewing" or users will be able to see/change the password.

Sub ProtectionOn()
 For Each sht In ActiveWorkbook.Sheets
  sht.Protect Password:="secret"
 Next
  ActiveWorkbook.Protect Password:="secret", Structure:=True
End Sub

Sub ProtectionOff()
 For Each sht In ActiveWorkbook.Sheets
  sht.Unprotect Password:="secret"
 Next
  ActiveWorkbook.Unprotect Password:="secret"
End Sub


Report •

#2
June 14, 2010 at 06:44:47
Hello DerbyDad03

Actually i didn't work
I gives me Syntax Error


Report •

#3
June 14, 2010 at 09:50:13
Hi,

There is an extra comma at the end of the third line.
Remove ',' at the end of this line:
sht.Protect Password:="secret",

Regards


Report •

Related Solutions

#4
June 14, 2010 at 10:25:03
Where?

I don't see any extra comma. ;-)


Report •

#5
June 14, 2010 at 11:07:49
DerbyDad03

Very funny!

Regards


Report •

#6
June 14, 2010 at 23:03:20
Hi,

it works after removing the comma
thanks


Report •

#7
June 17, 2010 at 04:32:35
Hey

how can i be sure that no one will use the ProtectionOff macro to unprotect all??

can i prevent users from running the macro??


Report •

#8
June 17, 2010 at 07:46:45
I covered that in my first response when I said:

"Don't forget to set the VBA project properties to "Lock Project for Viewing" or users will be able to see/change the password."

Look here.

Note the section that says that the workbook must be Saved, Closed and Reopened for the VBA Project protection to take effect.


Report •

#9
June 21, 2010 at 02:38:13
My concern is that still any one can press ALT F8 and run the macro Protectionoff and then can access and change all the file
locking project for viewing doesn't prevent users from running macros

Report •

#10
June 21, 2010 at 10:16:05
You can also require a password to allow the code to run to completion.

It doesn't have to match the other password.

Sub ProtectionOff()

 myPw = Application.InputBox("Enter Password to Enable This Macro.", _
                             "Macro Is Protected")
  If myPw <> "LetItRun" Then Exit Sub
 
 For Each sht In ActiveWorkbook.Sheets
  sht.Unprotect Password:="secret"
 Next
  ActiveWorkbook.Unprotect Password:="secret"
End Sub



Report •

#11
June 21, 2010 at 23:23:38
Thanks

this is exactly what i wanted


Report •

#12
July 19, 2010 at 00:36:10
Hello Derby

I was wondering if the last macro you have written to me can be modified so as the user can format rows and columns of the protected sheets.

to be more specified i want to enable setting column width and row height while the sheets are protected.


Report •

#13
July 25, 2010 at 01:03:09
Can anyone support on my last inquiry?

Report •

Ask Question