Protect: Formulas?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

(PeteCresswell) - 14 Nov 2006 18:56 GMT

I'm protecting a sheet via VBA.

There seem tb quite a few parms to control what gets allowed/disallowed, but I
can't find anything that applies to formulas.

I want the user to be able to put formulas behind some cells.

Right now, with the sheet protected, the user can type data into cells, but the
Sigma icon that allows formulas tb put behind them is disabled.

Unprotecting the sheet enables the icon... but I want various things to remain
protected.

Is there a way to allow formulas, but still have Protected=True?

Signature

PeteCresswell

Reply to this Message

Earl Kiosterud - 14 Nov 2006 22:42 GMT

Pete,

You have to have unlocked the cells the user is allowed to change (Format -
Cells - Protection). Unlocked cells, with the sheet protected (Tools -
Protection - Protect sheet), can have formulas entered manually, but the
Autosum button and function wizard (the dropdown button in the Autosum
button in Excel 2002) isn't available at all.

It appears you're straddling the line between design time (protected) and
data entry time (not).

Signature

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

> I'm protecting a sheet via VBA.
>
[quoted text clipped - 13 lines]
>
> Is there a way to allow formulas, but still have Protected=True?

Reply to this Message

(PeteCresswell) - 15 Nov 2006 01:31 GMT

Per Earl Kiosterud:

>You have to have unlocked the cells the user is allowed to change (Format -
>Cells - Protection). Unlocked cells, with the sheet protected (Tools -
[quoted text clipped - 4 lines]
>It appears you're straddling the line between design time (protected) and
>data entry time (not).

Oh well....

Thanks for resolving my issue... I guess I'll just stop fighting Mother Nature
and leave the sheet unprotected.

The protection thing was CYA. It's a data entry template that gets imported
into a little database and I wanted to be as certain as possible that the user
hadn't shifted any rows/columns around. Got invisible column/row counts to
check against plus a few other sanity checks... so it's probably gonna be ok.

Signature

PeteCresswell

Reply to this Message

Roger Govier - 15 Nov 2006 09:29 GMT

Hi Pete

>I wanted to be as certain as possible that the user
> hadn't shifted any rows/columns around.

Only a part of your thread is showing up on my machine (I seem to be
missing lots of many threads recently), so I don't know whether the
following might have already been suggested.

If you set the Scroll area on a sheet, then users cannot insert rows or
columns nor move them around. I use this often to stop my users from
inserting rows.

Something like
ThisWorkbook.Worksheets("Sheet1").ScrollArea = "F1:Q500"
set the range to suit your requirement

Signature

Regards

Roger Govier

> Per Earl Kiosterud:
>>You have to have unlocked the cells the user is allowed to change
[quoted text clipped - 24 lines]
> check against plus a few other sanity checks... so it's probably gonna
> be ok.

Reply to this Message

Rate this thread: