Uprotected but VBA Error - Why?

[RGK]

I have a VBA macro that has been working thus far, and I'm pretty sure it
worked after I added sheet protection, but suddenly I get errors on a
simple cell write.

I press a button, and a macro is supposed to do a few things including
write a number to an innocent enough looking cell.

The macro starts with:

If Worksheets(MySheet).ProtectionMode = True Then
Worksheets(MySheet).Unprotect
End If

And away it goes doing some stuff. Because of this annoying error, I put
a msgbox just before the troublesome line:


MsgBox "Protection status is: " & ActiveSheet.ProtectionMode

Then the line that causes the failure:

Worksheets(MySheet).Range(DBSZspot).Value = DBSize


and DBSize is just a simple one or two digit integer. It worked fine
until recently.

When I run it, the msg box tells me:

"Protection status is False"

And then the error:

"Runtime error 1004 - The cell or chart
you are trying to access is protected
and therefore s read only...."

and so on about protecting sheets.


Any ideas? If I turn off the protection before running, it runs fine
(still getting my debug message box that claims the "Protection status is
false")

How can the write fail due to protection, when the debug message clearly
shows that the code sees it as unprotected?


-Ross

 

[LRL]

In my macros which also modify a protected sheet, I don't bother with
the test to see if it's protected or not. I always call Unprotect, then
protect at the end of the macro. If your sheet may or may not be
protected and you need to restore it to that state after your macro,
then you'll probably have to do the test in order to know how to
restore the protect/unprotect state.

But if you don't care, try calling Unprotect and Protect all the time.
Don't know why the ProtectionMode would be lying to you though.

Leslie

 

[RGK]

I do the test more for clean-ness. During development the state
(protected vs. unprotected) could be either way when I run the macro. And
later if someone unprotects (I'm not using a password) then it might be
uncertain. But if calling unprotect on an unprotected sheet doesn't throw
an error - you're probalby right, it's easier to just ignore the test.

Anyway - that doesn't seem to have any impact on the weird protection
issue on an unprotected worksheet though.

Wish I knew why it was lying. Googling ferociously, I see a few people
grasping for answers to similar problems over the past 2 years, but no
answers ever seem to be posted to the problem. Sounds like on the PC it
was an issue for people on Win2000, but not other systems.

Still no answer though really. Anyone out there know what's up with this?

 

[JE McGimpsey]

How can the write fail due to protection, when the debug message clearly
shows that the code sees it as unprotected?

If you look at VBA/Help, you'll see that .ProtectionMode is true only if
the UserInterfaceOnly protection mode is enabled, so if you protect the
worksheet with UserInterfaceOnly off, .ProtectionMode will return False,
even though the sheet itself is protected.

If UserInterfaceOnly *is* enabled, you don't *need* to unprotect the
sheet - that's what the UserInterfaceOnly mode provides - the ability to
protect the sheet from the user interface, but allow changes to be made
using VBA.

If you want to check for protection of any sort, you should check the
..ProtectContents, .ProtectDrawingObjects, or .ProtectScenarios
propert(ies). If you're just using the default .Protect method to
protect the sheets, checking .ProtectContents is usually sufficient.

 

[JE McGimpsey]

But if you don't care, try calling Unprotect and Protect all the time.
Don't know why the ProtectionMode would be lying to you though.

It's not. ProtectionMode refers *only* to the UserInterfaceOnly mode.

 

[LRL]

I'm using protection mainly as a way to discourage the user from
changing the form. But if he really must, then all he has to do is
unprotect the sheet and have at it. There's no password even. I
encourage the user then to re-protect the sheet. But there doesn't seem
to be a menu equivalent of the UserInterfaceOnly option for protection.
Worse, if he does just protect the sheet via the menu. The macros will
all fail when they try to modify the sheet.

I could either provide a button to run a protection macro or tell him
to go into VBE and issue the right command. Neither will prevent him
from using the *wrong* protection.

The documentation for UserInterfaceOnly mentions only VBE for changing
this property. Is there an option I'm missing here?

I'm assuming that if I have to do a test for the kind of protection,
that I might as well just bracket all the macros with
unprotect/protect.

Thanks,

Leslie