Excel formulas in cells

  • Thread starter Thread starter Don Niall
  • Start date Start date
D

Don Niall

Hi,

I think the answer to this is no - but not sure ....

If I have a spreadsheet with multiple individual cells
with formulas applied, is there anyway I can 'protect' the
rule - so that if someone inadvertently inputs data into
that particular cell the formula is protected?
I cant protect the worksheet as parts of it are
specifically for data entry - and I dont believe there are
ways to protect sub-sets of cells ...?

Thx,

Don-
 
Don Niall,

The answer is yes.

Default each cell is locked. That means that after setting the protection
of the sheet, the cell can't be changed.
However you can set each cell to "not locked" via Format / Cells / Tab
protection ; uncheck Locked.

If you now set the sheet to protected, these cells can be cahnged, so are
available for input.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Don,

By default, all cells are locked, so that when you protect a
worksheet, all cells are inaccessible. You can lock only a
subset of cells by first selecting all cells on the worksheet
(CTRL+A), go to the Format menu, choose Cells, then the
Protection tab. There, clear the "Locked" setting. Then, go to
the Edit menu, choose Go To, then the Special button. In that
dialog, choose Formulas. This will select those cells containing
formulas. Next, go to the Format menu, choose Cells, and then
the Protection tab. Click the Locked setting. This will lock
those cells. Finally, protect the worksheet.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Chip,

Apologies! There was one little detail I didn't mention :
( ... This VERY nearly worked a treat .. However at the
end of each month I want to be able to cut/paste this
section for the start of a 'new' calendar month ... While
your instructions worked a treat it wont allow mw to cut
and paste the section (as its protected). I guess the only
way around this is to temporarily unprotect, then
cut/paste, then protect again ? A wee bit cumbersome ....
is there an easier way that you are aware of?

Thanks for your help!

Don-
 
Don

Once a month is "cumbersome"?

For one-button operation stick these two macros in your workbook and place
buttons on your Toolbar, assign the macros to them and run once a month.

Sub SHEETPROTECT()
ActiveSheet.Protect Password:="justme", _
DrawingObjects:=True, Contents:=True, Scenarios _
:=True
End Sub

Sub SHEETUNPROTECT()
ActiveSheet.Unprotect Password:="justme"
End Sub

Gord Dibben Excel MVP
 
-----Original Message-----
Hi,

I think the answer to this is no - but not sure ....

If I have a spreadsheet with multiple individual cells
with formulas applied, is there anyway I can 'protect' the
rule - so that if someone inadvertently inputs data into
that particular cell the formula is protected?
I cant protect the worksheet as parts of it are
specifically for data entry - and I dont believe there are
ways to protect sub-sets of cells ...?

Thx,

Don-
.
Don, if I understand your question correctly you want to
Click to expand...
protect part of your worksheet while leaving other areas
open for data entry. If you select the areas that you want
open for data entry you can exclude them from protection
by going to "format", "cells", clicking on the protection
tab and unchecking the "locked" box. After unlocking those
specific cells you can protect the balance of the sheet
using "tools", "protection", "protect sheet" and keying a
password.

Now your sheet should be protected except the specific
cells that you left "unlocked".

Hope that helps.
Mike
 
Hi Don

You could add a button to the toolbar which runs a procedure stored in
personal.xls similar to

Sub protect()
On Error GoTo finish
If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect: Exit
Sub [NB one line]
If ActiveSheet.ProtectContents = False Then ActiveSheet.protect: Exit
Sub [NB one line]
finish:
End Sub

which makes the job of protecting / unprotecting less cumbersome. You can
include passwords as necessary....

HTH & best rgds
Chris Lav
 
Back
Top