"Protect Sheet" #3

I am using Excel 2007 with Vista Home Premium.

I have 15 workbooks, each with 12 sheets. I want each sheet (not the
workbook) password protected to prevent any accidental to interference with

With a total of 180 worksheets I am a bit disappointed there is no way other
than protecting each sheet one at a time entering each password twice...!
......Or am I wrong? I sincerely hope I am.

Can anyone help?


2/26/2009 5:47:49 PM
excel 39879 articles. 2 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 4


There is no built-in way, however, you can write a VBA macro.

Sub ProtectMe()
   For each sh in Worksheets
      sh.Protect Password:="x"
  Next sh
End sub

If this helps, then click the Yes button

"Edward" wrote:

> I am using Excel 2007 with Vista Home Premium.
> I have 15 workbooks, each with 12 sheets. I want each sheet (not the
> workbook) password protected to prevent any accidental to interference with
> formulae.
> With a total of 180 worksheets I am a bit disappointed there is no way other
> than protecting each sheet one at a time entering each password twice...!
> .....Or am I wrong? I sincerely hope I am.
> Can anyone help?
2/26/2009 6:59:02 PM

Similar Artilces:

Protect Workbook Vs Protect Sheet
Hiii hope u are all ok. i have a problem about protect workbook.when i go to tools menus and then point to Protection and click on the protect work book to protect workbook can't be done as it should be.. probably i don't know the proper way how to protect a workbook........ plz help me . thank u very much indeed. (inadvance) Hi there, you can protect the workbook itself (against modification = read only, or even fully, against all access) when saving the file: file menu/ save as/ Tools (dropdown list on the panel right hand side) / settings and adding the PW hope, this help...

Disable Tools >Protection >Protect Sheet for all users but one
From other post I realize that you disable the menu item by: Application.CommandBars("Tools").Controls("Protection").Enabled = False but, I want to disable it for everyone unless you log on as Administrator. I have tried the following, but it does not work. Private Sub Workbook_Open() If Application.UserName <> "Administrator" Then Application.CommandBars("Tools").Controls("Protection").Enabled = False End If End Sub I cannot find the solution from other posts Application.username returns the name defined in Tools|Options|general Maybe ...

What is the difference between "protect sheet" and "protect workbook" in Excel? Gotta be homework but each worksheet is part of a workbook. -- Don Guillett SalesAid Software donaldb@281.com "DirtbykeDiva" <DirtbykeDiva@discussions.microsoft.com> wrote in message news:6804F7CA-7BB8-46EC-8AC8-714033CE99FE@microsoft.com... > What is the difference between "protect sheet" and "protect workbook" in Excel? Like the difference between locking the outside doors and locking the door on a single room -- Bernard Liengme www.stfx.ca/peopl...

unprotect sheet
Hi, I have a workbook with: protected sheets & protected worbook (diferent passwords) I use my password to open the workbook, and another password to unprotect the sheets. It seems that I forgot or mistyped one of the sheets, and it is always telling me that I am not entering the correct password to unprotect the sheet. So I need to unprotect a sheet in an open Excel workbook. can any one help Khalil Hi Khalil check out http://www.mcgimpsey.com/excel/removepwords.html it's quite scary how easy it is to unprotect the sheets :) Cheers JulieD "Khalil Handal" <seminar...

enabling and desabling autofiler on protect sheet
Hi all, how can i give the option in the VBA command to dynamicly enable or desable autofilter on a row when the cells are protected for any editing ...

excel vba
I have a workbook set up and i'm having a tough time with this protection stuff. I have tried different .unprotect methods but it doesn't work. I have a print button with coded conditions such as if cell a1 = "this" then certain cells get the borders formatted to be white, etc. then it prints one page, changes the format back and then prints 2 pages like that and saves the page. Anyways, bottom line is that there are format changes which are done before and after printing. My code is set up so that as soon as the person goes into the main page, it becomes protected in order t...

Protect sheet
Hi I protected a certain range but I would also like to disbale any entry in that area that is not in number format, HOW I know it is possible, I've seen in but don't know how to do it Thanx Ivic Ivica, If you mean allow only numbers, try Data -Validation - Whole number or Decimal. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Ivica" <anonymous@discussions.microsoft.com> wrote in message news:600EE201-0A2B-49A6-A38D-A6020A94162D@microsoft.com... > Hi, > > I protected a certain range but I wou...

the unprotect sheet is not highlighted
I protected a set of worksheet in a work book and now I need to "unprotect" them but the "unprotect sheet" is not highlighted. Please help -- confused have you unprotected the workbook first then tried to unproect the work sheet? -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Maddie" wrote: > I protected a set of worksheet in a work book and now I need to "unprotect" > them but the "unprotect sheet" is not highlighted. P...

Protect Sheet vs. VBA Code
I have a data entry sheet which has VBA code to automatically place some formulas in certain cells. I wish to lock certain columns, then protect the sheet prior to turning it over to a user. There are some columns which I wish to protect which are also automatically filled by the VBA code. With these columns locked, and the sheet protected, I get an error when the VBA code attempts to fill the cells in these columns. Is there a way to let VBA perform and still have these columns protected? Thanks. --- Message posted from http://www.ExcelForum.com/ Hi Steveski! Use: Worksheets(&quo...

How To Protect Sheet For SHIV
SHIV I cannot reply to your posting but this is what you need to do:- To lock all the cells in a Worksheet in EXCEL 2007 take the following action:- 1. Hit Ctrl-1 to launch Format Cells. 2. Protection tab. 3. Remove the green tick from the box called:- Locked - then hit OK. 4. Highlight all the cells in your Worksheet by clicking in the box to the left of A and above 1 in the top left hand corner. This should cause your Worksheet to become highlighted. 5. Ctrl-1 to launch Format Cells once more. 6. Protection tab. 7. Place a green tick from the box ca...

Unprotect Sheet Password Lost
One of my users has inadvertently protected a worksheet (i.e. doesn't know how he did it) while the AutoFilter was on. He has no idea what the password may be. Is there any way to override this? Since the data was filtered, it can't even be copied and pasted in to a new sheet. Thanks for any help. Thanks, this was just what we needed! DJ "J.E. McGimpsey" <jemcgimpsey@mvps.org> wrote in message news:jemcgimpsey-53D5AE.09074202092003@msnews.microsoft.com... > Check out > > http://www.mcgimpsey.com/excel/removepwords.html > > In article <#hH#MMW...

Protect sheet but allow sorting only
I'm using Excel 2003, and I've tried to allow only sorting on a protected sheet. I've tried by choosing Tools, Protection, Protect Sheet. Next, I've checked the sort check box in the "Allow all users of this worksheet to:" area. The only other check boxes that are checked are the "Select locked cells" and "Select unlocked cells" boxes. When I test the protected worksheet I am still unable to sort. Am I missing something? Thanks! -- Christina "Christina" <Christina@discussions.microsoft.com> wrote in message news:E37A3...

How to protect sheet layouts and formatting?
I have a spread sheet setup to display values from lots of other sheets within the same workbook. only 77 cells require a user to input data, so I have set up protection so only these cells can be changed when protect sheet is enabled. problems I would love help with, please: 1) how can I now protect the formatting of these cells from being changed? but still allow users to input data? 2) stop other users from, renaming or deleting any other sheets within the workbook needed for this sheet to work, or inserting rows or columns in them? 3) all without password protecting the file? Kind reg...

I have a sheet that has rows and columns that are grouped. I need to protect rollup portion of the grouped rows. However, when I do that and protect the sheet, I can no longer group and ungroup. How can I allow the user to group and ungroup rows and columns but protect the grouped row? Example: row 4 is a rollup of MY DATA with the next 10 rows as supporting/detailed data. I have created a group so that I can contract 9 of the rows and show only the rollup MY DATA or expand it and show all the subordinate data. The formulas in the columns MY DATA are protected while the cells in the supporti...

Protect sheet problem
When I open the workbook, Sheet CALCULATEHERE is not protected properly. "Select locked cells" and Select unlocked cells" are both checked. Can someone explain what I'm doing wrong? Thanks, Bernie Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheets("CALCULATEHERE").Select UNPROTECTSHEET Range("A1") = 0 Range("A2") = 0 Range("A4").Select DELETEWORKSHEETS Sheets("CALCULATEHERE").Select PROTECTSHEET 'When I check the sheet here, it is protected properly, but something apparently goes...

Protect sheet against sorting only
OK, I want to protect a sheet against sorting only, eg i want the user to be able to do everything else, just not sort the data. is there an easy way to do this, alternativly is there code that will do the same thing? Thanks Hi A potential solution might be to select all the sheet cells and go Format>Cells>Protection tab and make sure the Locked checkbox is unchecked, then apply Protection but leave Sort unchecked (obviously). Then users should be able to do pretty much everything except Sort. Hope this helps! Richard On 30 Jan, 00:58, colli...@hotmail.com wrote: > OK,...

Re-protect sheet
Code below works fine, but need to re-instate password after changes are made. Sub SwitchView_n_protect2() ActiveSheet.Unprotect Password:="jeremy" With ActiveSheet.Range("f:f,g:g").EntireColumn .Hidden = Not .Hidden End With ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True try this: Sub SwitchView_n_protect2() With ActiveSheet .Unprotect Password:="jeremy" With .Range("f:f,g:g").EntireColumn .Hidden = Not .Hidden End With .Protec...

cannot protect sheet with auto filter
Hi, heres what I have xp home xp office suite. I am using a sheet for grocery list database with auto filter. the headers at the top are like "quantity", "Item", "catagory". Normally I can select cells that I want to enter data and unlock them. then protect the sheet. but...with an auto filter If I select the headers mentioned above and lock them, then select all other cells and unlock them. the auto filter pull down list's dont pull down cause the headers are locked. Im explaining this the best I can but I may be leaving something out. Anyone? Greg ...

Protect sheet help
I have a worksheet was setup to allow users to edit certain ranges when worksheet is protected. I deleted one of the ranges which users should not edit it anymore from the “Allow Users Edit Ranges…” window; however, the deleted range was editable even when the worksheet was protected. Just to experiment, I deleted all ranges from the “Allow Users Edit Ranges…” window, therefore, users should not be able to edit the entire worksheet when the worksheet is protected, and however, I was able to edit all ranges that were pervious setup to allow users to edit. Any ideas? Check that the cells are...

Protect Sheet after an entry.
I have created a macro that allows users to enter specific info at the beginning with a form. When they click on OK, the sheet is protected but a specific range of cells is unlocked so that they can paste specific data from another program. I want the entire sheet to become protected (or to lock the cells again) as soon as the user pastes this values to a cell. How can I do this? Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ In a Worksheet_Change event you c...

Protect sheet #3
I am working on an Excel document witch is a big database. I would like to set a password with the "protect sheet" option in the Tools/Protection menu. "Normal" users who will open the database won't be able to edit the document, but will rather open it in "read only" mode. The problem is that I still want these users to be able to use the "auto filter" and also use the "sort A-Z" option. I have tried to select these options in the configuration of the protection (protect sheet window) but it doesn't work. I can't do any of those tw...

Protect sheet #2
Hi, I have a worksheet with checkboxes that are linked to cells. I trie to protect the cells but then I am not able to change between true an false. Is there a way to protect them so the user does not mess up th linkage? Many thanks. Marco ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Can you move them to another worksheet (maybe hidden). Or maybe you could dedicate a column to the links and then just hide that column. They user could still get to those cells, but...

help with protect sheet
good day all,i have a invoice template with a macro set to number them.when i protect the sheet the macros stops working.any suggestions? thx wyn -- wynb WYN Unprotect the sheet, do your stuff then reprotect. Sub Numberit() ActiveSheet.Unprotect Password:="justme" 'your code or macro name goes here ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord Dibben Excel MVP On Wed, 16 Feb 2005 15:55:01 -0800, WYN <WYN@discussions.microsoft.com> wrote: >good day all,i have a...

Form and protect sheet
Hi - I have a worksheet and colleagues prefer to input data (including new data) by using the 'Data - Form'. However, if I protect the sheet (Tools - Protection - Protect Sheet), then the option to create a new record or delete an old one is grayed out in the form. Is there a way round this? I am using Excel 2003. Any advice would be appreciated. Brian. Maybe you could provide them with a macro that Unprotects the worksheet Shows the Data|form Reprotects the worksheet. You could start by recording a macro when you do it manually. Then you could plop a button f...

edit a comment while protect sheet is on the cell is unprotected
i want other useres to b able to enter /edit a comment in an unprotected cell when protect sheet is on When protecting the sheet enable "Edit Objects" in the allow users to list. Gord Dibben MS Excel MVP On Thu, 4 Feb 2010 10:20:02 -0800, Rubber 4 u <Rubber4u@discussions.microsoft.com> wrote: >i want other useres to b able to enter /edit a comment in an unprotected cell >when protect sheet is on ...