Custom Views and Protected Sheets - 2003

[Clif McIrvin]

Using Excel 2003 SP3 (XP Pro SP2)

I'm trying to combine the use of views and protection and they don't
seem to play well together.

The purpose for using protection is primarily for data entry
convenience -- tab skips over protected cells -- and also to protect
formulas from accidental changes.

I am using views to hide columns and define print areas / headers and
footers.

To my dismay, protecting the worksheet breaks views -- evidently because
protection prevents unhiding rows and columns. Is there a setting to
allow hiding and unhiding columns in a protected worksheet?

Are there other approaches to accomplishing this that I can investigate?

As I write this, it occurs to me that XML transforms (I just said more
than I understand) might be what I'm looking for --- if so, can you
point me along the pathway to learning how to use XML capabilities?

Thanks in advance,

 

[Gord Dibben]

Generally one works around this by having a macro unprotect the sheet,
switch to the Custom View then re-protect.

Assign the macro to a button.

ActiveSheet.Unprotect
ActiveWorkbook.CustomViews("myview").Show
ActiveSheet.Protect


Gord Dibben MS Excel MVP

 

[Clif McIrvin]

That makes sense. I find it hard to shake the 'gotta save every bit of
space I can' habits that were developed back when 76 kb was the
avaialable disc storage ... and 16k was a huge amount of RAM.

Thanks.