Error running Macro when sheet is protected

[tber83]

I am getting an error when I protect a worksheet and save it. Upon re-opening the worksheet, I try to use the cells that are being formatted by a macro, but I get the following error...

Run-time error '1004':

Unable to set the NumberFormat property of the Range Class.


If I open the worksheet and it is already protected from when I last closed it, then I get the error. If I unprotect the sheet, then close it and reopen it, then I don't get the error. Does anyone know what I might be missing or doing wrong. Any help would be great. Thanks!

 

[Jonmo1]

macros can't change protected cells, just the same as a user can't change a protected cell.

But you can use code to UNprotect the sheet, then do some code, then reprotect the sheet.

Like

Sheets("Sheet1").Unprotect Password:="PasswordGoesHere"
'Rest of your code goes here
Sheets("Sheet1").Protect Password:="PasswordGoesHere"

Hope that helps..

 

[tber83]

Hey,

Thanks for the reply. I was just looking through my worksheet and all of the cells that I am running the macro on are not protected. I went through and unprotected all of them using the format cells feature under the protection tab. If I understood you correctly, the macro should work on these cells even if the rest of the worksheet is protected? Please let me know. Thanks again.

 

[Jonmo1]

Honestly, stay away from the wild goose chase of figuring out which cells are causing the macro to not work, or why the macro won't work when the sheet is protected.

Just add the lines of code to your macro to UNprotect and Reprotect the sheet. Many headaches shall be avoided.

 

[tber83]

I tried to add the code before and after my code to protect and unprotect the worksheet. Now I am getting this error ....

Compile error:

Invalid outside procedure

I'm not really sure what that means, but it comes up whenever I try to make any changes while the new code is in place.

 

[dave3009]

I'd suggest posting your code but I have a hankering the protection lines are in the wrong place

Sub MySub()
Sheets("Sheet1").Unprotect "Password"

'Do Stuff with code here

Sheets("Sheet1").Protect "Password"
End Sub

 

[Jonmo1]

The lines would have to go INSIDE the sub procedure,
Between the Name Line and the End Sub Line

Sub MacroName()
Sheets("SheetName").Unprotect "PasswordHere"
 
'Rest of your code goes here
 
Sheets("SheetName").Protect "PasswordHere"
End Sub

 

[Tahas]

Hello jonmo1,

I am also having trouble in using this code. My file name is Cheque Pick up Form and I use the code as;
Sheets("Cheque Pick up Form").Unprotect ""

Upon using, I get the error message "Run-time error9.. Subscript out of range

Many thanks in advance for your kind help.

 

[Peter_SSs]

Hello jonmo1,

I am also having trouble in using this code. My file name is Cheque Pick up Form and I use the code as;
Sheets("Cheque Pick up Form").Unprotect ""

Upon using, I get the error message "Run-time error9.. Subscript out of range

Many thanks in advance for your kind help.

I think there may be an issue with terminology here. A Workbook is made up of one or more Worksheets.

1. Please clearly identify the name of the Workbook that contains the protected Worksheet. Maybe it is 'Cheque Pick up Form.xls'?

2. Please clearly identify the name of the protected sheet. Maybe it is 'Cheque Pick up Form'?

2. I suspect that your code is being run from a different Workbook than the answer to Q1. Please confirm whether that is the case or not.

 

[Tahas]

Thanks Peter SSs,

It worked. It was my mistake in entering the file name.

Tahas