Protect Cells #3

Is there a way to protect / unprotect multiple sheets at 
the same time.  If not, any suggestions about the next 
best thing.

Appreciate the help.

Jon
0
anonymous (74719)
2/24/2004 7:35:05 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
565 Views

Similar Articles

[PageSpeed] 1

JT, you can with a macro, like this

Sub protect_sheets()
 Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect
Next ws
 End Sub

 Sub unprotect_sheets()
 Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect
Next ws
 End Sub

Or with password

Sub protect_sheets()
 Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect password:="123"
Next ws
 End Sub

 Sub unprotect_sheets()
 Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect password:="123"
Next ws
 End Sub

-- 
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

"JT" <anonymous@discussions.microsoft.com> wrote in message
news:0bc901c3fb0d$4db303b0$a101280a@phx.gbl...
> Is there a way to protect / unprotect multiple sheets at
> the same time.  If not, any suggestions about the next
> best thing.
>
> Appreciate the help.
>
> Jon


0
pbridgesnews (182)
2/24/2004 7:49:06 PM
Reply:

Similar Artilces:

Quicken Password protected
I just got Money 2004 Deluxe, I am trying to import my data from Quicken and the program says there is a password for my quicken account that is required, but I never had a password. No way to get past this. Any ideas? If your data file is Q04, Money04 can only deal with Q03 or older. Otherwise, who knows? "Stephen Weiss" <stephenaweiss@comcast.net> wrote in message news:012f01c3cb22$9a957ac0$a101280a@phx.gbl... > I just got Money 2004 Deluxe, I am trying to import my > data from Quicken and the program says there is a password > for my quicken account that is req...

linking cells in a column (result) to cells in a row (source)
MS XP Pro / Excel 2007: Good day all. I have data in two consecutive rows. The 1st one is text and the second contains formulae which results in values. (Call these the "source") On another sheet in the same workbook I have two columns (Call these the destination) which need to be linked to the source rows if value (other than zero) is present in the 2nd source row. There could be zero value or blank cells within the source rows. I need code to sequentially seek values (other than zero) in the cells in the 2nd row of the source and when found, the next available cell ...

protecting outlined data
I want to be able to use the expand/collapse function in outlines when the worksheet is protected. Copy this macro in a normal module It will run automatic when you open the workbook You can also use the workbook open event Sub auto_open() With Worksheets("sheet1") .Protect Password:="rbelecki", userinterfaceonly:=True .EnableOutlining = True End With End Sub Look in the VBA help for more information about protect(userinterfaceonly) -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "rbelecki" <rbelecki@l...

Password Protect Outlook #2
I work in an offcie environment and sometimes have to leave my PC unattended for just a few minutes. Unfortunately some confidential emails have been read by my staff and I need a way of safeguarding them other than turning off the PC, is there any way I can password protect access into my Outlook 2000, so all I do is close Outlook. (Sorry still operating under W95) Many thanks You can set a password on a Personal Folders File. If the folder list isn't visible in Outlook, click View | Folder List. Right-click the root folder (usually "Personal Folders" or "Outlook T...

Sort whith 3 row column heading
I've searched the forum on sorting and can't find this problem. My shee in Excel 2002 has three rows of data that serve as column headings. need for others to be able to easily sort using the toolbar sort keys. When I do this, it doesn't recognize the column headings and sorts the too. If I choose the data range myself, it works - but the users nee a one touch simple method. Any ideas -- Message posted from http://www.ExcelForum.com Kathy, You need your column headings in one row. You can use Alt-Enter as you enter them to break into separate lines within the cell, or set ...

Adding cells with a particular comment
Hi all, is there any simple to add all cells in a row which have a particular comment without using macro. pls help. Regards, NC HI, Use the SumIF function -- anurag. ----------------------------------------------------------------------- anurag.d's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=895 View this thread: http://www.excelforum.com/showthread.php?threadid=31476 well i can use it for particular values but how to use it with comments. My problem is a particular comment for e.g "reject" or "accept" ia added to that cell, & ...

Cell ref is it possibile to calculate / change this in a formula
Any SUPER USERS uot there? I am trying to construct a formula where I need to copy it down 6500+ rows but I need one of the cell refs to be calculated in sted of having its row number to raise by one for each row copied. sample, Normel coping: =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25) =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C26) What I am trying to get: =IF(AND(F25<=201012,F25<>$F$1),B25,'MASTER tot'!C25) =IF(AND(F26<=201012,F26<>$F$1),B26,'MASTER tot'!C16) For every line the for...

Password protection on split database
I have a database which is split into front end and back end. The front end is password protected but the backend isn't Is it possible to protect both with the same password? I've tried protecting the backend with a password but when I open the front end it can't "see" the backend. Any ideas? Thanks Tony Put a password on the backend, then, after opening the front-end, use the Linked Table Manager to update the links (just like you would if you had moved the backend). You should be prompted for the 'missing' password during this process. You should only...

How to protect 2. axis in an Excel Chart to stay if data changes
A Pivot table is created on Tab1 in an Excel workbook. On Tab2 there is a Line Chart created with data from Tab 1 using 2 axis. If data in Tab1 change the 2nd axis on Tab 2 disappears, jumps ober and overrules axis 1. How can I protect chart area with 2 axis being able to change referring data in the pivot table. ...

pivot table empty cell
I need to have an empty cell be in the pivot table. I have multiple pivot tables I have generated for a report. I have created a worksheet that reads the data from the pivot table (so it looks nicer). I have tried to check the box in options that says to put a 0 or another symbol (*) in an empty cell and it doesn't work. I want people to be able to input their own data and refresh the pivot table. If I have four response options (sometimes, always, never, usually) and the new data does not have data for one of the options then the table that reads the data gets messed up with its numb...

Protection
Hi everyone OK my problem is I am using an offie computer that everyone has access to and we cannot - and do not want to - change this. Is there a way that I can put a password for my profile on Microsoft outlook so that no one can read the new AND the old messages found in my inbox? You can put a password on your PST file, presuming you use a PST file... Emad Kamel wrote: > Hi everyone, > OK my problem is I am using an offie computer that everyone has > access to and we cannot - and do not want to - change this. Is there > a way that I can put a password for my profile on Micr...

limit no of characters in cell
How can I limit the number of charaters (including spaces) in a cell to 48? Additionally, I'm not sure if this is possible, say it applied to cell A1, once 48 characters are reached, it will then automatically drop down to the cell below to A2 and do the same again. thanks -- rocket0612 ------------------------------------------------------------------------ rocket0612's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19492 View this thread: http://www.excelforum.com/showthread.php?threadid=375572 Try validation from the Data menu, and select text length...

IN CELL CALCULATIONS
DONT KNOW IF I CAN DO THIS BUT.. IM TRYING TO ADD A FORMULA INTO A CELL TO SPLIT THE NUMBER I PUT INTO THAT PERTICULAR CELL, FOR INSTANCE IF I ENTER THE NUMBER 2 INTO A CELL I NEED IT TO THEN SPLIT THAT NUMBER INTO FRACTIONS, THIS IS SO I CAN CALCULATE MY FOOD STOCK, AS IF I NEED TO CHECK STEAKS FOR INSTANCE, I MAY HAVE ONLY SOLD HALF FOR A PERTICULUR MEAL SO IF I ENTER THAT 2 HAVE BEEN SOLD, I NEED THE CELL TO CALCULATE THAT IN TOTAL IVE ONLY SOLD ONE WHOLE STEAK... Hi! It sounds to me as if you need to settle on a safe system of units. Fo example, if the smallest unit of steak is half a s...

CANNOT PROTECT
Trying to password and read only protect a Microsoft Word docuement that is embedded in Excel. When I open the "protected" document, the read-only prompt does not appear...and I can still make changes and close the docuement. HELP! Try this: create the document directly in word, protect it and save it to disk. Then copy the text you want and past it special as an OLE object in excel Peter ...

Spreadsheet password protection
How do you password protect a spreadsheet. I can't find anything in the help section. You can prevent un authorised access by giving a password to open the workbook itself, so that no one else can open it (Go to Tools, Options and then Security Tab). You can do it there if you have already saved the file, or you can go to "save as" then "Tools" and "General Options" where you can give password. On the other hand you can protect work sheets and ranges for different people by using "Tools" and then "Protection" Tab -- M Imran Buha...

How to protect cells?
I would like to protect a range of cells (A2:D24) with a password for a group of users that would make changes to cells in this range. Then on the same sheet, also protect a range of cells (E2:E24) from everyone but me. Is this possible? Thanks. Mark, Please don't multipost. See the thread in .programming. John "Mark F." <m7829@yahoo.com> wrote in message news:FSzQb.10797$6o4.894@fe2.texas.rr.com... > I would like to protect a range of cells (A2:D24) with a password for a > group of users that would make changes to cells in this range. Then on > the same she...

Protecting a Worksheet
Does anybody know how to protect a workbook so that a user CANNOT unhide a sheet, CANNOT add rows to the viewable sheet, CANNOT alter locked cells, but CAN add text to a comments column which will expand and Wrap text as the comments increase in size? I understand how to protect/unprotect locked cells, but I cannot find the right combination for the above functionality. Is it even possible? Any help is greatly appreciated. Jeff When a _Workbook_ is protected (Tools, Protection, Protect Workbook, Structure) sheets cannot be unhidden. When a _Worksheet_ is protected locked cells ca...

How to navigate within a formula without changing cell references?
I have the following function in G20: =IF(C20>0,F20,"") I need to replace the second parameter (F20), with another IF statement. =IF(C20>0,IF(F20<>"",F20,"Error"),"") I go to G20 and press F2 to edit the formula. I move the cursor to just before the "F20" using the arrow keys and start typing in the new IF statement. When I have it entered, I need to move the cursor over the old F20 to finish the new IF statement, but Excel takes the arrow keys to mean I want to reference an adjacent cell. How can I get Excel to interpre...

Pivot Table questions #3
Okay, I'm not that new to Pivot Tables, but normally when I create them, I just live with what I can't figure out. Now I'm wanting to really create some very specific ones. I need two pivot tables that I will be printing essentially as reports. My data looks like this. Store Item Size Qty Price Extension 1 ABC 32 2 9.00 18.00 1 ABC 34 2 9.00 18.00 1 AXX 32 1 10.00 10.00 2 ABC 32 1 9.00 9.00 Okay, what I want is Pivot table that looks like this: Item Size Price Extension 32 34 ABC 3 2 9.00 45.00 AXX 1 10.00 10.00 Total 55.00 So...

Count Cells Within 10 of a Cell Value
I need a formula that calculates how many out of a range of cells are within 10 of another cell. For instance, I have cells F3 through U3. I want to know how many of them are either 10 above or 10 below the value in cell V3. If cell V3 equals 20, than I want to know how many are between 10 and 20 and how many are between 20 and 30. Ideally, this would be in the same formula but two seperate formulas is also acceptable. Look in the help index for SUMIF. You can combine -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Missbrooke06" &...

stopping pasted text deliminate across multiple cells
When pasting text that contains back slashes, text is split into multiple cells using the back slash as a deliminator, this occurs automatically and I wish to stop it. Pasting as text or unicode text via paste special makes no difference. Example text 'C:\Program Files\Common Files\Microsoft Shared' splits into 4 cells in a row and the back slashs removed Using Excel 2003 SP1 Paul, Did you do a Text to Columns with the back slash as the delimiter ? look at this: -- Regards, Juan Pablo Gonz´┐Żlez Excel MVP "Paul from NZ" <Paul from NZ@discussions.microsoft.com> w...

How do I pass series data x-value range to a chart from a cell?
I have a series on a line chart (x-values "=Sheet1!$A$1:$A$10"). I also have two worksheet cells (B1 & B2) that contain the start and end date for the x-axis of the chart. I want to be able to link the chart series data to the start and end date values so that the chart automatically adjusts when I change the dates in the worksheet cells. I can create a string or address for the x-values but how do I do not know how to pass this to the chart series?? I know I can do this using a macro, but I want to do it without macros so that friends who use MicrosoftCharts (the cheap, n...

Password protected sheet access
I have a spreadsheet protected with a unknown password. How do I access this sheet? Ps: the "read only" option is not available. Thanks, Rivane. Hi Rivane Is this the password for opening the file, or the one for unlocking locked cells in the already open sheet ? Best wishes Harald Excel MVP Followup to newsgroup only please. "Rivane Cardim" <rivane.cardim@exxonmobil.com> wrote in message news:0a8801c35dae$a03275a0$a301280a@phx.gbl... > I have a spreadsheet protected with a unknown password. > How do I access this sheet? > > Ps: the "read only...

Formula omits adjacent cells: ignore a whole row at once?
Allowing space to add further rows in a spreadsheet gives me a whole row of totals where the formula is tagged as 'formula omits adjacent cells'. How can I get the thing to ignore the lot at once rather than having to open all the drop down dialogues one at a time? Cheers, S On Jul 18, 8:19 am, "spamlet" <spam.mores...@invalid.invalid> wrote: > Allowing space to add further rows in a spreadsheet gives me a whole row of > totals where the formula is tagged as 'formula omits adjacent cells'. How > can I get the thing to ignore the lot at once r...

[Excel 2003] problem in files with pivottables after install Office Service Pack 3
Hi all. System: Windows XP Pro SP2, Office 2003 Professional + Service Pack 3 I have a problem with some xls files after install office service pack 3. With service pack 2 this files normal open. With service pack 3 after open file displays dialog (my translate from russian) "In workbook ... have contents which can not be read. Try restore contents of workbook ? If you trust source of this workbook, press button YES". If I press "No" - file not opens. If I press "Yes", displays next dialog. This dialog form content a list of corrections in file. Biggest pa...