Strange problems with setting hidden property of a range when sheet protected with UserInterfaceOnly

Hi,

I have run into a problem that makes no sense. I have protected my
sheets using the UserInterfaceOnly=True parameter for some time, and it
has worked fine until now. I'll illustrate the problem with a few
examples.

When the document is opened, in the workbook_open() function, I can do
the following without problem:

Sheet1.Unprotect
Sheet1.Protect UserInterfaceOnly:=True
Sheet1.Range("A1").EntireRow.Hidden = True

So here the UserInterfaceOnly protection seems to work OK. However,
when I later execute the following code in Sheet1:

Sheet1.Range("A1").Value = 123
Sheet1.Range("A1").EntireRow.Hidden = True

.... Setting the value of the locked cell A1 works fine, but trying to
hide the row, I get the "Cannot set hidden property of range object"
error.

I also tested doing a unprotect+protect right before the calls, but it
didn't change anything. Unprotecting the sheet without reprotecting it,
did however make the problem disappear, so it is somehow related to the
protection.

Another odd thing is that the VB Editor does not automatically fix the
case of the userinterfaceonly-parameter. Case is corrected for other
parameter names. It does show it in the parameter list popup, though.
I'm using Excel 2002.

Anyone encountered anything like this? Ideas for debugging? Is there a
way to query the status of the userinterfaceonly-bit for a worksheet?

The easy answer first.

You can use:
msgbox sheet1.ProtectionMode

to check that status.

Is sheet1 protected with a password? If it is, I've found xl2002 to be more
stringent. I had to use the correct password when setting that
userinterfacemodeonly flag. (xl2k didn't seem to care about the password.
xl2002+ cares a lot.)

And try this to get the correct capitalization.

Type this on an empty line somewhere:
Dim UserInterfaceOnly
hit enter
then delete the line

But this case problem won't affect your macro.


[email protected] wrote:
>
> Hi,
>
> I have run into a problem that makes no sense. I have protected my
> sheets using the UserInterfaceOnly=True parameter for some time, and it
> has worked fine until now. I'll illustrate the problem with a few
> examples.
>
> When the document is opened, in the workbook_open() function, I can do
> the following without problem:
>
> Sheet1.Unprotect
> Sheet1.Protect UserInterfaceOnly:=True
> Sheet1.Range("A1").EntireRow.Hidden = True
>
> So here the UserInterfaceOnly protection seems to work OK. However,
> when I later execute the following code in Sheet1:
>
> Sheet1.Range("A1").Value = 123
> Sheet1.Range("A1").EntireRow.Hidden = True
>
> ... Setting the value of the locked cell A1 works fine, but trying to
> hide the row, I get the "Cannot set hidden property of range object"
> error.
>
> I also tested doing a unprotect+protect right before the calls, but it
> didn't change anything. Unprotecting the sheet without reprotecting it,
> did however make the problem disappear, so it is somehow related to the
> protection.
>
> Another odd thing is that the VB Editor does not automatically fix the
> case of the userinterfaceonly-parameter. Case is corrected for other
> parameter names. It does show it in the parameter list popup, though.
> I'm using Excel 2002.
>
> Anyone encountered anything like this? Ideas for debugging? Is there a
> way to query the status of the userinterfaceonly-bit for a worksheet?

--

Dave Peterson

Thanks for the reply, Dave.

I just found a strange workaround for the problem. If I activate any
other worksheet before trying the set the hidden property, everything
works. Even activating another sheet and then again the original one is
ok. This has to be some obscure bug in Excel.

Anyway, it works now, and that's good enough for me. I'd like to find
out what causes this, but deadlines won't wait



Dave Peterson wrote:
> The easy answer first.
>
> You can use:
> msgbox sheet1.ProtectionMode
>
> to check that status.
>
> Is sheet1 protected with a password? If it is, I've found xl2002 to be more
> stringent. I had to use the correct password when setting that
> userinterfacemodeonly flag. (xl2k didn't seem to care about the password.
> xl2002+ cares a lot.)
>
> And try this to get the correct capitalization.
>
> Type this on an empty line somewhere:
> Dim UserInterfaceOnly
> hit enter
> then delete the line
>
> But this case problem won't affect your macro.
>
>
> [email protected] wrote:
> >
> > Hi,
> >
> > I have run into a problem that makes no sense. I have protected my
> > sheets using the UserInterfaceOnly=True parameter for some time, and it
> > has worked fine until now. I'll illustrate the problem with a few
> > examples.
> >
> > When the document is opened, in the workbook_open() function, I can do
> > the following without problem:
> >
> > Sheet1.Unprotect
> > Sheet1.Protect UserInterfaceOnly:=True
> > Sheet1.Range("A1").EntireRow.Hidden = True
> >
> > So here the UserInterfaceOnly protection seems to work OK. However,
> > when I later execute the following code in Sheet1:
> >
> > Sheet1.Range("A1").Value = 123
> > Sheet1.Range("A1").EntireRow.Hidden = True
> >
> > ... Setting the value of the locked cell A1 works fine, but trying to
> > hide the row, I get the "Cannot set hidden property of range object"
> > error.
> >
> > I also tested doing a unprotect+protect right before the calls, but it
> > didn't change anything. Unprotecting the sheet without reprotecting it,
> > did however make the problem disappear, so it is somehow related to the
> > protection.
> >
> > Another odd thing is that the VB Editor does not automatically fix the
> > case of the userinterfaceonly-parameter. Case is corrected for other
> > parameter names. It does show it in the parameter list popup, though.
> > I'm using Excel 2002.
> >
> > Anyone encountered anything like this? Ideas for debugging? Is there a
> > way to query the status of the userinterfaceonly-bit for a worksheet?
>
> --
>
> Dave Peterson