Code to protect/unprotect a sheet using a macro with password

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet that I created that requires a lot of sorting and other
macros. However with the worksheet protected I can't perform the functions. I
need the worksheet password protected so the user can't mess up the formulas.
Therefore, I need to un-protect the worksheet, sort the rows and then
re-protect the worksheet. I can do it if I give the user the password but
then they can just un-protect it. Therefore I want to have the password in
the macro so it is done automatically. Help!
 
One way:

Const csPWORD As String = "drowssap"
ActiveWorkbook.Worksheets("Sheet1").Unprotect Password:=csPWORD
'Sort
ActiveWorkbook.Worksheets("Sheet1").Protect Password:=csPWORD
 
FredH, something like this,

Const PW As String = "123" 'Change Password Here

ActiveSheet.Unprotect Password:=PW

'you code here

ActiveSheet.Protect Password:=PW



And protect the VBA project so they can't see the password there, like this

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is at the top-left of the spreadsheet
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 said:
FredH, something like this,

Const PW As String = "123" 'Change Password Here

ActiveSheet.Unprotect Password:=PW

'you code here

ActiveSheet.Protect Password:=PW



And protect the VBA project so they can't see the password there, like this

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is at the top-left of the spreadsheet
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



The following are my two macros. I don't have any sort codes in them. The protect macro puts the password test in but the unprotect macro doesn't work.
Click to expand...
 
Paul B said:
FredH, something like this,

Const PW As String = "123" 'Change Password Here

ActiveSheet.Unprotect Password:=PW

'you code here

ActiveSheet.Protect Password:=PW



And protect the VBA project so they can't see the password there, like this

To protect the VBA project, from your workbook right-click the workbook's
icon and pick View Code. This icon is at the top-left of the spreadsheet
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



Below are the macros i use. The protect macro works but the upprotect doesn'tSub Unprotect()
Click to expand...
'
' Unprotect Macro
' Macro recorded 4/6/2007 by Charles Hord
'
' Keyboard Shortcut: Ctrl+u
'
ActiveSheet.Unprotect Password:="test"
Range("A2").Select
Application.Run "'Macro test.xls'!Protect"


End SubSub Protect()
'
' Protect Macro
' Protects sheet password is test
'

'
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="test"

End Sub
 
It wasn't totally clear from the above but I had the same issue where the sub
unprotect wouldn't work.

I realised it was because PW was only defined within "sub protect" but not
within "sub unprotect".

Fixed easily by defining PW within both subroutines.
 
Back
Top