protect sheet macro problems

N

Nikki

Hi,

I have a macro that runs to insert rows when a button is clicked in my
sheet. This works fine when the sheet is unprotected but not when the sheet
is protected. Is there a way to protect certain cells in my sheet but have
it unprotected so my macro works?

Nikki
 
G

Gord Dibben

Nikki

If running Excel 2002 or higher you should be able to Protect the sheet with
Allow insert rows enabled under Tools>Protection>Protect sheet.

If running an earlier version you will have to Unprotect in code then do the
inserting then re-protect.

Sub yourmacro()
ActiveSheet.Unprotect Password:="justme"
'do the inserting code
ActiveSheet..Protect Password:="justme"
End Sub


Gord Dibben MS Excel MVP
 
S

ShaneDevenshire

Hi Nikki,

It is not necessary to unprotect and reprotect a sheet to allow the macro to
run against it, instead protect the sheet with the VBA below:

ActiveSheet.Protect Password:="mypass", UserInterfaceOnly:=True

From the help system: UserInterfaceOnly Optional Variant True to protect the
user interface, but not macros. If this argument is omitted, protection
applies both to macros and to the user interface.
 
Top