Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-26-2011, 01:43 PM
furface00 furface00 is offline Protecting the formatting and formulas in a worksheet Windows XP Protecting the formatting and formulas in a worksheet Office 2007
Novice
Protecting the formatting and formulas in a worksheet
 
Join Date: Feb 2011
Posts: 14
furface00 is on a distinguished road
Default Protecting the formatting and formulas in a worksheet


Good day,

In Excel 2007 I am wanting to protect the formatting and formulas in a worksheet that will be used by two other people. I have tried various settings all without luck.

Imagine that you have set the formatting in column A, row 6:450, to Date. The type is 3/14/10 (dd/mm/yy). I want to retain this formatting and allow someone to input dates. The problem is that they can accidentally change the formatting or paste some other information into any cell in that column. So how does one keep the formatting set?

In my worksheet I have 34 columns which I would like to protect. All 34 columns require data entry of one sort or another. Some straight text, others numeric inputs.

Insofar as formulas, I have found a partial solution by just hiding the columns with my formulas, but is there a better way to protect them than just simply hiding?

Thanks for your help

DD
Reply With Quote
  #2  
Old 02-26-2011, 05:42 PM
Kimberly Kimberly is offline Protecting the formatting and formulas in a worksheet Windows 7 Protecting the formatting and formulas in a worksheet Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Protecting a sheet in Excel involves two things... worksheet protection and cell locking. Cells are locked by default, so you need to unlock the ones you want users to be able to work with, before you turn on worksheet protection. You might choose to select all the cells, unlock them, and then lock the ones with formulas... this will allow users in all but locked cells.
Then go to the Review tab and click Protect sheet, making sure you don't allow them to change formatting. The password can be left blank.
Reply With Quote
  #3  
Old 02-27-2011, 05:41 AM
furface00 furface00 is offline Protecting the formatting and formulas in a worksheet Windows XP Protecting the formatting and formulas in a worksheet Office 2007
Novice
Protecting the formatting and formulas in a worksheet
 
Join Date: Feb 2011
Posts: 14
furface00 is on a distinguished road
Default

Thanks for your reply Kimberly.

The method you described certainly can protect the formatting, however it does not allow for any data input into those cells.

Excel 2007 has one option under the protect sheet icon and that brings up the protect sheet dialogue box. This does not offer any options for protection of formatting.
Under the protect workbook icon there is the option to protect structure and windows, neither of which will protect the formatting in cell and allow for data input.

I would like to have the worksheet setup so that individuals who will be entering data cannot accidentally change the formatting in a cell. How does one setup the sheet to allow for formatting consistency and data entry?

I have used your method for protecting my formulas. Work great!

Thanks

DD
Reply With Quote
  #4  
Old 02-27-2011, 01:03 PM
Kimberly Kimberly is offline Protecting the formatting and formulas in a worksheet Windows 7 Protecting the formatting and formulas in a worksheet Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Before enabling sheet protection, unlock any cells they should have access to. This is on the Protection tab of the Format Cells dialog box.

After I click Protect Sheet, I cannot change any formatting on the sheet at all. I can only edit the content of cells that have been unlocked.

Regarding
Quote:
one option under the protect sheet icon and that brings up the protect sheet dialogue box. This does not offer any options for protection of formatting
:

When you click Protect Sheet, don't you get a list of things you can choose to allow the user to do, and Format Cells is one of them? Do not check the box for Format Cells and they won't be able to change your formats.
Reply With Quote
  #5  
Old 02-27-2011, 01:57 PM
furface00 furface00 is offline Protecting the formatting and formulas in a worksheet Windows XP Protecting the formatting and formulas in a worksheet Office 2007
Novice
Protecting the formatting and formulas in a worksheet
 
Join Date: Feb 2011
Posts: 14
furface00 is on a distinguished road
Default

Here is a simple experiment. Open a new sheet in Excel. Click on the arrow in the upper left corner of the spreadsheet to highlight the entire worksheet. Right click and choose Format Cells >>> Protection >>>Unlock.

Now every cell is unlocked. Highlight A1-A6. Right Click and choose Format >>> Date >>> 3/14/01 for a format. (mm/dd/yy)

Now go to protect sheet.The protect sheet dialog box appears. Make your selection and choose ok. In my version of Excel 2007, I have many options, the 2 which are checked by default are Select locked cells and Select unlocked cells. The list continues but all after the top two are not checked, including format cells.

Click ok. Now in e5 type in some gibberish. Press return, than copy the contents of that cell and paste it into any cell from a1-a6. Format has now been changed.

Or try to enter the date 17/3/11 which is not according to format. What happens? Format has changed, Why? How do we prevent this?

Thanks

DD
Reply With Quote
  #6  
Old 02-27-2011, 02:22 PM
Kimberly Kimberly is offline Protecting the formatting and formulas in a worksheet Windows 7 Protecting the formatting and formulas in a worksheet Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Sorry, as far as I know, you cannot prevent the behavior you are describing. You can use Data validation to force typed entries to be dates, but validation is not triggered by pasting, so I think this is of limited value to you. (Also, dates are just numbers, so numbers also get a pass.)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Protecting the formatting and formulas in a worksheet Protecting or hiding ASK fields SaneMan Word 1 01-14-2011 08:16 PM
Protecting the formatting and formulas in a worksheet IF Formulas mizzamzz Excel 1 07-08-2010 02:32 AM
Protecting the formatting and formulas in a worksheet Protecting your logo in Word TimD Word 5 04-29-2010 07:10 AM
Protecting the formatting and formulas in a worksheet Protecting image in word 2007. aligahk06 Word 1 10-12-2009 08:22 AM
Protecting Word Document by restricting access permissions! user Word 0 11-20-2008 01:21 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft