can't hide columns

[foxguy]

I just started getting an error message that I can't hide columns.

In the Worksheet_Activate() event I have some entire columns being hidden (I:IT). It has worked fine for over a year, and it works fine on other sheets in the same file.
It gives Err number 1004, which is no help. It just says that columns weren't hidden, but doesn't give me any reason for why they weren't hidden.

Are there any typical reasons why the columns can't be hidden?

[Colin Legg]

Is the worksheet protected?

[foxguy]

The worksheet is protected with UserInterFaceOnly:=True.

[foxguy]

I just closed and reopened the worksheet, and now it hides the columns. I should have thought of that first.

[Colin Legg]

Does the UserInterfaceOnly protection get kicked in when the workbook is opened? I guess it got lost somehow...

[foxguy]

I spoke too soon. I reopened an earlier version of the file. When I opened the correct file, the problem was still there.

[Colin Legg]

If you run this test:

• Manually unprotect the sheet
• Manually try to hide those columns

Do you get an error about shifting objects?

[foxguy]

I can't imagine how the UserInterfaceOnly could have gotten lost. My Workbook_Open() event puts it onto every sheet, and I never manually protect a sheet (I'm not even sure I remember how). I always use a macro attached to a menu button to protect sheets.

[foxguy]

As it turns out. I did try earlier to insert columns and got the about to shift objects error. I did not insert the columns. Instead I just inserted in the rows that I was working in, and had no problem.

[Colin Legg]

As it turns out. I did try earlier to insert columns and got the about to shift objects error

Okay, that explains the RTE then. Perhaps there are some cell comments which are causing the problem?

http://support.microsoft.com/kb/q170081/

[foxguy]

Now this is weird.
There are no comments on the sheet.

I tried manually hiding the the columns and got the error can't shift object off the sheet.
I have a macro attached to a menu button that toggle the hidden property of the sheet and it worked fine (it didn't work before which is what triggered this thread). And now I can manually hide the columns with no error.

[foxguy]

What is RTE?

[Colin Legg]

RTE = Runtime Error

Are there any other objects (hidden or visible) floating on the sheet?

[foxguy]

Spoke too soon again. I still had the earlier version open and was working on it.

The real file will not hide the columns manually or by macro.

There are Active-X buttons on the sheet, but they are all to the left of the columns I'm trying to hide.

[Colin Legg]

I found an updated MS article:
http://support.microsoft.com/kb/211769

See if anything rings true?

[foxguy]

The article didn't help, but it did give me an idea.

I think I found the problem.

I have a comment in column "I" (that I forgot about), and the comment is about 4 columns wide. When I try to hide columns that would force the display of the comment to extend beyond the visible columns I get the error. As long as the comment can display within the visible area of the sheet, then no problem.

And it doesn't matter whether the comment is visible or not.

[foxguy]

I found a solution.

I widened column IV wide enough to accomodate the entire comment in column I, and now I can hide all the columns between I and IV.

Thanks for the assist.

[Colin Legg]

Yes, that comment is the problem (I did ask about comments earlier!). Glad you got it sorted.

[foxguy]

I had forgotten that there was 1 comment on the sheet. But since it was to the left of the columns I was trying to hide, I would have bet that it didn't make any difference.

Well you learn something new every day.

Thanks again