Excel 97 - Group and Outline on a Protected Worksheet problems.

[beeawwb]

Hi all.

I'm currently designing an Excel spreadsheet at work, and everything is
exactly the way I want it when unprotected. However, my group and
outline (1 group for each Quarter of the year (Q1, Q2, Q3, Q4) and 1
group for Yearly summary (QSummary)) does not expand when I protect the
worksheet. The worksheet MUST be protected as I have some extremely
large formulas and chart data that I need locked / hidden, but I want
the users to be able to select what quarter they enter data into.
Currently, I have to unlock the worksheet every time I want to expand a
new section.

Does anybody know a way that I can protect my formulas from being
changed while still allowing me to expand / contract outlines?

An afterthought. Maybe it is possible to unprotect the worksheet when I
click on the EXPAND button, and reprotect it when I click on a cell
with a formula. The problem I have with the protect when clicked (if a
formula exists in that cell) (which I can use, but don't want to) is,
that I have to be in a non-formula cell to expand, and I cannot protect
things like labels.

Thanks,
-Bob

 

[BrianB]

You need to add code to momentarily unprotect the sheet while changes
are made, and then re-protect.

Therefore a custom button or whatever will be required.

 

[beeawwb]

I already came up with a temporary solution, but shot it down once I
finalized the sheet.

My solution was this.

Using code from Exceltip, I did the following.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
For Each rng In Target.Cells
If rng.HasFormula Then
ActiveSheet.Protect
Exit Sub
Else
ActiveSheet.Unprotect
End If
Next rng
End Sub

And then made it so that all my "Labels" that I did not want changed
were =IF(A1="","LABEL NAME,""). This worked fine, as long as I did not
protect the sheet with a password. But once I put a password on the
sheet, clicking in a cell with out a formula (hence, one I want users
to put data into) asks for the password.

Is there any way to either a) Use the above code, but input a password,
even into the code, and then password protect the code to hide it, so
that, to manually unprotect the sheet, I would still have to input my
password, or

b) Another was to do what I require?

Thanks for reading,

-Bob

 

[beeawwb]

Hey, cool, I figured it out.

My workstation doesnt have VBA_XL8.hlp, so I have to figure it ou
manually, but I found out that ActiveSheet.Protect has a passwor
argument.

So,

ActiveSheet.Protect ("passwordname")

and

ActiveSheet.Unprotect ("passwordname")

Now replace the code seen previous. My next question starts a ne
thread.

Thanks for the help!

-Bo