My Macros will not work after I protect my document.

[Samantha]

Is there any way to make macros work once my document is protected? If so how?

 

[RyanH]

If you are changing anything on the worksheet and the worksheet is protected
then you will get an error. You need to Unprotect the worksheet, run your
code, then Protect it again.

Sub Protect90()

Sheets("Sheet1").Unprotect "password"

'your code here

Sheets("Sheet1").Protect "password"

End Sub

Ryan

 

[Samantha]

What?
Where would I put that? I have multiple macros on each sheet of my
workbook...am i supposed to put this in each of my macros? what will this
macro do?

 

[mike]

In my experience, if you protect the workBOOK, macros will not run. They
should still run with a protected worksheet though.

 

[Samantha]

No im protecting the workSHEET

In my experience, if you protect the workBOOK, macros will not run. They
should still run with a protected worksheet though.

 

[mike]

Try saving as file type 'macro enabled workbook' in the save as filetype
dropdown. That worked for me with a protected sheet with macros.

 

[Jim Thomlinson]

Anything you can not do manually on a protected spreadsheet will also not
work in a macro... The only exclusion to that rule is if you protected the
sheet in code and added the parameter UserInterfaceOnly:=True. At that point
macros will have some abilities that can not be done manually. That being
said UserInterfaceOnly is a bit hit and miss so it may not be worth doing.

 

[Jim Thomlinson]

Add that code at the beginning and the end of each macro. You need to do it
for each sheet you intend to modify. If you want to post one of your macros,
we can add the necessary code to show you what we mean...

 

[RyanH]

Does an error pop up? If so, click "Debug". Then you will see a line at the
top of the procedure that looks something like "Sub Macro1()". Under that
line put this code:

Dim sh As Worksheet

For Each sh In Worksheets
sh.Unprotect
Next sh

'your macro syntax here

then, at the end of the procedure you need to put this:

For Each sh In Worksheets
sh.Protect
Next sh

End Sub

NOTE: If you entered a password when you protected the worksheet add "
Password:= "Your Password Here" directly next to sh.Unprotect and sh.Protect

Hope this helps!

 

[Samantha]

Sub Create_New_Entry()

Rows("24:24").Select
Selection.Insert Shift:=xlDown
Range("E23:R23").Select
Selection.AutoFill Destination:=Range("E23:R24"), Type:=xlFillDefault
Range("B24").Select
End Sub

 

[Samantha]

Sub Create_New_Entry()

Rows("24:24").Select
Selection.Insert Shift:=xlDown
Range("E23:R23").Select
Selection.AutoFill Destination:=Range("E23:R24"), Type:=xlFillDefault
Range("B24").Select
End Sub

 

[RyanH]

Sub Create_New_Entry()

Activesheet.Unprotect

Rows("24:24").Select
Selection.Insert Shift:=xlDown
Range("E23:R23").Select
Selection.AutoFill Destination:=Range("E23:R24"), Type:=xlFillDefault
Range("B24").Select

Activesheet.Protect

End Sub

 

[Gord Dibben]

Mike

Is this a feature of Excel 2007?

In earlier version. macros will run on protected workBOOKS


Gord Dibben MS Excel MVP

 

[mike]

sry for the off topic Samantha . . .

I was using 2007 when I found that my macro wouldn't run on a protected
workbook. It took me days to figure out what was goin wrong and several hours
in this forum - No one was able to help me.

 

[rpeniche]

Mike... did you ever find out what to do to be able to run macros on a
password protected workbook? Would appreciate your help before I go crazy!

Roger.