Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / June 2007

Tip: Looking for answers? Try searching our database.

Protect formula cells

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
CAM - 12 Jun 2007 01:46 GMT
Hello,

I am using Excel 2003 and I am wondering if there is a easy way in
protecting formula cells in my workbook.  I have about 25 formula cells and
it is all over the workbook.  My users keep changes the formula cells, they
suppose to enter data not changing formulas.  I have about 20 workbooks to
do.  Is there an easy way or global way to protect formulas using a password
on a sheet?   I don't want to go cell by cell.  Can anybody help me on this
one?  Your help will be appreciated,  thank you in advance.
Roger Govier - 12 Jun 2007 08:28 GMT
Hi Alex

The default property for all cells on a sheet is Locked.
You would need to
select the cells on a sheet where you want users to be able to enter
data
Format>Cells>Protection>uncheck Locked
Tools>Protection>Protect sheet.

Now the only cells where users can enter anything, is in the cells you
have specifically unlocked.

You might want to do it the other way around, by selecting all cells in
the sheet by pressing the small gray cell above row 1 and left of column
A. This will select the whole sheet. Format all cells as unlocked.
Select your cells with formulae, set property to locked, then protect
sheet.

Signature

Regards

Roger Govier

> Hello,
>
[quoted text clipped - 6 lines]
> cell by cell.  Can anybody help me on this one?  Your help will be
> appreciated,  thank you in advance.
CAM - 12 Jun 2007 14:29 GMT
Thanks

> Hi Alex
>
[quoted text clipped - 23 lines]
>> anybody help me on this one?  Your help will be appreciated,  thank you
>> in advance.
Gord Dibben - 12 Jun 2007 17:07 GMT
CAM

If you want to speed Roger's process up a bit..................

You could use a macro that would lock just the cells with formulas on each sheet
in a workbook.

Sub protect_formulas()
Dim wks As Worksheet
Dim wkbk As Workbook
Dim cl As Range
Set wkbk = ActiveWorkbook
    For Each wks In wkbk.Worksheets
       wks.Cells.Locked = False
           For Each cl In wks.UsedRange
              If cl.HasFormula Then
           cl.Locked = True
       End If
     Next cl
wks.Protect
Next wks
End Sub

Gord Dibben  MS Excel MVP

>Thanks
>
[quoted text clipped - 25 lines]
>>> anybody help me on this one?  Your help will be appreciated,  thank you
>>> in advance.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.