Excel protection & macros

N

Neil Pearce

Dear all,

I would like to protect my spreadsheet so that others might use it. However
when I do so the macros in the workbook do not run. I may select the button
that they are attached to but the marco itself doesn't run.

Which settings do I change to alter this situation?


Thanks in advance,

Neil
 
J

John Bundy

You have to turn off protection, run code, then turn it back on all through
code. Just record a macro of you protecting the sheet, then unprotect it.
Look at the code that is created in the module and you will see what it takes
to unprotect. Put the unprotect at the beginning and the protect at the end.
 
D

Dave Peterson

Do the macros not even start or do they start and fail?

If they don't start, make sure your users are enabling macros when they open
your workbook.

If they start and then fail, then you may be trying to do stuff that isn't
allowed on a protected worksheet.

If that's the case, you can unprotect the worksheet at the top of your macro, do
the work and then reprotect the worksheet right before your macro ends.
 
N

Neil Pearce

That has worked well John, thank-you. However this doesn't enable me to set
a password within the workbook thereby others may still alter the
spreadsheet.

Is there a way to enable Macros to run if a set a password is imputted when
protection the workbook?
 
N

Neil Pearce

That has worked well Dave, thank-you. However this doesn't enable me to set
a password within the workbook thereby others may still alter the
spreadsheet. Is there a list somewhere of what is allowed and not allowed
when the spreadsheet is protected?

Is there a way to enable Macros to run if a set a password is inputted when
protection the workbook?
 
D

Dave Peterson

I don't understand.

If you have a worksheet protected, then you know the password. And I would
guess that you have both locked and unlocked cells in that worksheet.

The users should be able to change the unlocked cells on that protected sheet
with no problems.
 
G

Gord Dibben

Neil

Maybe this..............?

Sub dothings()
ActiveSheet.Unprotect Password:="justme"
'your macro code to do things
ActiveSheet.Protect Password:="justme"
End Sub


Gord Dibben MS Excel MVP
 
N

Neil Pearce

Perfect Gord, thank-you.

Gord Dibben said:
Neil

Maybe this..............?

Sub dothings()
ActiveSheet.Unprotect Password:="justme"
'your macro code to do things
ActiveSheet.Protect Password:="justme"
End Sub


Gord Dibben MS Excel MVP
Click to expand...
 
Top