Can't set the Entirerow.Hidden property.

[Jeremy]

This is weird, and I can't figure out why it won't work.

I have a worksheet that basically has a series of yes/no fields on it,
and when a user changes the fields, certain ranges hide or unhide. It
uses a couple of class modules and the worksheet_change event.

Now, I took one of the areas that's supposed to expand and collapse
and replaced the Yes/No trigger with a formula that chooses "Yes" or
"No" based on other conditions in the sheet. To keep the range
working correctly, I added code to check whether the conditions have
changed. If they change, the sheet tells the class to do its thing
and hide or unhide the range. This worked for a while.

Now, for some reason, everything seems to be firing properly, but when
that range and only that range gets to the point where it hits:

If .hidden then .hidden=false

or:

if .hidden=false then .hidden=true

it simply ignores it. Just on that range.

What's really funny (oh, it's fricken HILARIOUS) is that this object
is set up to basically go through and set all the hidden properties
every time certain cells change, so if I change the yes/no on any of
the other areas, this range hides or unhides just fine. It's only
when I try to trigger it based on the conditions that it totally fails
to work. But it's recognizing the conditions and correctly triggering
the hide/unhide procedure, so I don't have a clue what the problem is
here.

Therefore, I offer a prayer to the Excel Gods. Just for some new
ideas, naturally, because this is a messy problem and I don't expect
someone to know exactly what's wrong based on my poor and partial
description. Thanks!

 

[paul.robinson]

Hi
Do you have any comments or other shapes/buttons in this range? If you
do, edit them and look for format comment... or format object etc from
the right click menu. Click on "Move and Size with cells" - I'm
guessing "Do not move and size with cells" is clicked on. Columns and
rows won't hide if the object in them can't.
Banged my head on this one for many weeks once and it nearly broke my
heart trying to find out the problem!
regards
Paul

 

[Jeremy]

I didn't know about that, but I'll keep it in mind for future
reference. Unfortunately, in this case, everything seems perfectly
normal, even to the point that activating the hide/unhide procedure
anywhere else in the sheet, or even typing in "yes" or "no" in the
problem range, will hide or unhide it just fine. It seems like it
ignores the command to hide only when I call it from the code that
checks whether those other values change, and I even call it in
exactly the same way as I do elsewhere, but in this case, it won't
work for no apparent reason.

Does anyone know what else might cause VBA to ignore a change to the
Hidden property without returning an error?

 

[paul.robinson]

We would have to see the code I think.
regards
Paul