Trouble with protected sheets & formulas

[Peter Bernadyne]

Hello,

I have a macro which operates on a worksheet that I have protected.
There are some ranges in the worksheet which I have both locked an
hidden and there are others for which only the formulas are hidden (bu
the cells are not locked).

My macro needs to work on some cells (enter formulas, etc.) where
wish to hide the formulas the macro enters. These ranges I hav
designated as hidden but not locked.

The problem is, whenever I run my macro (which autofills for a give
universe), I only have an accurate calculation for the first row o
formulas entered in these hidden (but not locked) ranges. Thus, whe
the autofill is called, it is as though the autofill is working wit
values and not formulas (all the rows' results are identical to th
first one's).

Is this what hiding the formulas is supposed to do? Is there anyway t
work around this so that my macro can accurately perform calcluation
with autofills? If not, could someone tell me what code I should us
to habitually unprotect the sheet (with password = ...) and the
reprotect, please?

Thanks for any help any one can give.

-Pete

 

[Frank Kabel]

Hi
you may post the code you're currently using

 

[Peter Bernadyne]

Hi, thanks for replying.

Let me clarify. My worksheet has ranges that are hidden but not locke
and when I try to enter a formula into these ranges, I do it by enterin
a formula in the first row and autofilling the rest of the way down.
The only problem is that the autofill does not give me the vali
results, only the same values as in the first row.

I don't know if there is a workaround for this in Excel 2002 which i
why I'm thinking it might be better to turn protection off and then o
again during my macro. Here is code I can use for a given range but
don't know how to incorporate a password into for the sheet protectio
(as you can when opening a workbook, for example):

Columns("D:F").Select
Selection.Locked = False
Selection.FormulaHidden = True
ActiveSheet.Protect

I'm thinking perhaps the best way to accomplish my task is to make th
selection.formulahidden = false when autofilling and then revert bac
to true afterwards, but this is inefficient and I'm afraid that user
could break my macro in runtime and see the formulas I'm trying to hid
before the macro can hide my formulas again.

I know this is long, but I hope it clarifies things a bit.

-Pete

 

[Frank Kabel]

Hi
and automatic calculation is enabled?. And does it work on non hidden
cells?

 

[Peter Bernadyne]

Hi,

Yes, that's right. I thought that automatic calculation was no
enabled, too. However, I have checked that and it is, but it stil
doesn't work.

As for non-hidden cells, I have tried running my macro when the shee
is unprotected (thus making hidden false) and it works.

It is very strange

 

[Gord Dibben]

Peter

I have been testing this on Excel 2002 and 2003.

If the cells are set to unlocked and hidden then the sheet protected, formulas
do not replicate when autofilled.

Unprotect the sheet and you will see that all but the top cell are top-cell
value, not formulas.

Guess you will just have to unhide when doing the Autofill.

Gord Dibben Excel MVP

 

[Peter Bernadyne]

Thank you both for your help. It appears that this is the only option
am left with.

In this case, I was wondering if you could assist me with two issues:

1). I don't know how to incorporate a password in my macro usin
worksheet protection. I am familiar with:

ActiveSheet.Protect

but I don't know how to incorporate the password into my code t
protect and unprotect (as with Workbooks.Open and password:= ****, fo
instance).

2). Can you suggest a "security measure" such that if the macro shoul
be broken (as in Ctrl+Break) in runtime, which could reveal the formula
I am trying to hide, that the macro would automatically close th
workbook, unsaved and in protected form, to prevent users from gainin
access to the formulas on my worksheet this way?

I realize that this is a bit overkill, but the project I am working o
happens to be particularly sensitive.

Any advice would be gratefully received.

Regards,

-Pete