Custom View: hide/unhide does not work when worksheet is protected

[pwrichcreek]

In a protected worksheet I want to hide/unhide certain columns using Custom
Views. The column hiding/unhiding works fine as long as the worksheet is not
protected, but I get the following message and the columns are not properly
hidden/unhidden, when the worksheet is protected:

"Some view settings could not be applied".

I hoped that checking the checkbox "Format Columns" in the Protect dialog --
that is, allowing users to "format columns" when the sheet is protected --
would do the trick, but that does not seem to be the case. In fact, I tried
checking ALL THE CHECKBOXES and that did not allow hide/unhide columns. HELP
says:

"Format columns When cleared, prevents users from using any of the
commands on the Column submenu of the Format menu, including changing column
width or hiding columns."

The "Format Columns" DOES allow me to manually hide and unhide columns, even
when the sheet is protected, but it just doesn't happen automatically as part
of the Custom View.

Is there some other way to accomplish what I want?

I don't mind writing some code, but I'm unclear on where to put it. What
event or action would trigger just before a Custom View is carried out? Seems
I could turn off protection to let the Custom View hide/unhide the desired
columns. But then I would need to turn protection back on and I'm not sure
where/how that piece of code would get executed.

Any thoughts or suggestions will be appreciated.

TIA,

Phil

 

[JLGWhiz]

Other than the limited facilities provided for when setting the protection
parameters, you cannot make changes without unprotecting the sheet. That is
the purpose of the protection.

 

[pwrichcreek]

Thanks for your reply.

... you cannot make changes without unprotecting the sheet. That is
the purpose of the protection.

I know the purpose of protection. As I stated in my message, I set the
protection parameters to allow "Format Columns", meaning hide/unhide should
be allowed even when the sheet is protected.

The "Format Columns" DOES allow me to manually hide and unhide columns, >even when the sheet is protected, but it just doesn't happen automatically as part
of the Custom View

It seems logical that, if "Format Columns" allows me to hide and unhide
manually when the sheet is protected, then hide and unhide should also be
allowed when implied by the View -- hidden columns are a part of the view.
That logic would seem to be supported by the HELP information. But apparently
it just ain't so.

So I am resigned to the fact that, despite logic and despite what the HELP
says, I will probably need to write some code. The questions is where to put
such code.

Phil

 

[JLGWhiz]

Yes, I misread your original posting. The only two things I could see were
that if your sheet contains a List, then the custom view won't work. The
second was in the dialog box to add the view that the Hidden Columns, Rows
and Filter settings checkbox needs to be either checked or unchecked. If you
don't have a List and the checkbox is set right, then you should get your
view.

 

[Gord Dibben]

Sub changeviews()

ActiveSheet.Unprotect Password:="justme"
Application.Dialogs(xlDialogCustomViews).Show
ActiveSheet.Protect Password:="justme"

End Sub

Run the macro and select a view from the dialog box when it pops up.

Note: if the chosen custom view leaves you on another worksheet, that sheet
becomes the activesheet so you must trap for that or hard-code the sheetname.

Sheets("Sheet1").Unprotect Password:="justme"
Application.Dialogs(xlDialogCustomViews).Show
Sheets("Sheet1").Protect Password:="justme"


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Check for my other post coming through.

I forgot to tell you to place the code in a general module and assign the macro
to a button or shortcut key combo.


Gord Dibben MS Excel MVP