Protect: Formulas?

[(PeteCresswell)]

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?

 

[Earl Kiosterud]

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).

 

[(PeteCresswell)]

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 -
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).

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.

 

[Roger Govier]

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