Run-time error '1004' ... But only when the worksheet is protected?

[jonvanwyk]

When my worksheet is unprotected, my macro works perfectly, however after I protect it (and even though the cells affected by the macro are unlocked), I get the following error:

Run-time error '1004':
Unable to set the NumberFormat property of the Range class

When I click on debug, it shoots me to a portion of my VBA code highlighted in yellow:

Please Login or Register  to view this content.

Does anyone have any idea what is causing this? I should note that the macro still technically does what it is supposed to when the sheet is protected, its just that I also get the error pop-up.

[Mordred]

Is your offest going to a cell that is still protected?

PS, even though you have only one line of code displayed in your post, you should still use code tags around it.

[jonvanwyk]

Here is the whole story: I am using the following code to protect the cells from being modified by unauthorized persons:

Please Login or Register  to view this content.

This allows those with authority to modify the form, while allowing other users only to click on macro buttons that identify them based on their windows user login; entering their name into a cell and then time stamping the cell next to their name. This allows people to request days off, or volunteer for overtime on a schedule.

Technically, I wouldn't even need to protect the worksheet since they cannot click on any cells based on the code above, except that they can still right click on the unprotected macro buttons and alter its settings or move it around on the screen, delete them, etc. My solution to this was to only lock the macro buttons, leaving all of the actual cells unlocked since they already cannot be clicked on; and then protect the worksheet. I noticed that I even get the error I describe when the worksheet is protected, but nothing is formatted to be locked. The line of code in my post above that gets highlighted yellow when I click on "debug" is a part of the following sub:

Please Login or Register  to view this content.

Thoughts?

[jonvanwyk]

Hello? *tap *tap *tap ... screeeeeeaaaaaaaaaach ... is this thing on?

[Umpal]

Excel, macro, run-time error '1004' Unable to set the NumberFormat property of the Range class
MS Visual Basic 6.0 Runtime Service Pack has to be installed.
Look for VB6RuntimeSP6.exe.

Cheers
Piotr

[TMS]

You have a couple of options.

1. you can unprotect the sheet before the code and protect it again afterwards:

Please Login or Register  to view this content.

or

2. you can use InterfaceOnly

Please Login or Register  to view this content.

You would, if you have not done so already, need to protect your VBA Project with a password.

Regards, TMS

[OnErrorGoto0]

Did you allow formatting cells when you protected the sheet?

[TMS]

@OEG0: I'd have thought the OP wouldn't want users to format the sheet, but that's a guess. The formatting is being applied in the code ... at least, that's the desire.

Regards, TMS

[OnErrorGoto0]

@TMS
I know that. My point is that if the asker has not allowed formatting cells, the code cannot do it either (unless using UserInterfaceOnly:=True as you mentioned) even if the cell is not locked, which is why the error occurs. The asker did ask what the cause was...

[TMS]

@OEG0: ah, fair enough. Unprotecting and Protecting is my preferred method ... never really got my head around InterfaceOnly

[OnErrorGoto0]

Mine too. UIO is unreliable, especially where things like data validation or objects are concerned.