how to enable a macro in protected sheet

S

Steve Eastham

I have a two page spreadsheet which has limited # of cells for users to input
data, but then need to run a macro to process the data. With the worksheets
protected, the macro will not run. Does anyone know of any workarounds?

Thanks

Steve Eastham
 
M

Mike H

You really need to be a bit more specific becuase protecting a worksheet
won't stop a macro running but could stop it running as expected. For example
if the macro tried to alter a protected cell an eror would occur.

You could simply un-protect using code as soon as your macro kicked off and
then protect again at the end of the macro.

Sub amacro()
Worksheets("Sheet3").unProtect
'do your stuff
Worksheets("Sheet3").protect
End Sub

Mike
 
P

Paul B

Steve, one way is to let the macro unprotect and protect the sheet,
something like this

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

ActiveSheet.Unprotect Password:=PW

'you code here

ActiveSheet.Protect Password:=PW



You will also need to protect the VBA project so you can't see the password
in it.

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
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
 
Top