Re: Unprotecting Sheets

Is it possible to unprotect all sheets in a workbook at the same time?
0
xdsouza (5)
11/25/2008 4:56:07 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
393 Views

Similar Articles

[PageSpeed] 27

Zee,

You could use VBA to cycle through all the sheets that are protected and 
unprotect them that way.  Don't think it is possible without code.
-- 
http://www.ExcelHelp.us
ed@ExcelHelp.us
888-MY-ETHER ext. 01781474



"Zee" wrote:

> Is it possible to unprotect all sheets in a workbook at the same time?
0
ed5274 (22)
11/25/2008 8:30:01 PM
Unfortunately I am not very familiar with VBA and therefore am not sure what 
to enter into the editor to accomplish this.

"galimi" wrote:

> Zee,
> 
> You could use VBA to cycle through all the sheets that are protected and 
> unprotect them that way.  Don't think it is possible without code.
> -- 
> http://www.ExcelHelp.us
> ed@ExcelHelp.us
> 888-MY-ETHER ext. 01781474
> 
> 
> 
> "Zee" wrote:
> 
> > Is it possible to unprotect all sheets in a workbook at the same time?
0
xdsouza (5)
11/25/2008 8:47:02 PM
Zee,

The following code should do it:

public sub unlock
  for each sht in thisworkbook.sheets
     sht.unprotect
  next
end sub
-- 
http://www.ExcelHelp.us
ed@ExcelHelp.us
Call me at
1-888-MY-ETHER ext. 01781474



"Zee" wrote:

> Unfortunately I am not very familiar with VBA and therefore am not sure what 
> to enter into the editor to accomplish this.
> 
> "galimi" wrote:
> 
> > Zee,
> > 
> > You could use VBA to cycle through all the sheets that are protected and 
> > unprotect them that way.  Don't think it is possible without code.
> > -- 
> > http://www.ExcelHelp.us
> > ed@ExcelHelp.us
> > 888-MY-ETHER ext. 01781474
> > 
> > 
> > 
> > "Zee" wrote:
> > 
> > > Is it possible to unprotect all sheets in a workbook at the same time?
0
ed5274 (22)
11/26/2008 3:51:01 AM
Hi Galimi,

I tried pasting the code into the VB editor and the first line states an 
error.

Thanks

"galimi" wrote:

> Zee,
> 
> The following code should do it:
> 
> public sub unlock
>   for each sht in thisworkbook.sheets
>      sht.unprotect
>   next
> end sub
> -- 
> http://www.ExcelHelp.us
> ed@ExcelHelp.us
> Call me at
> 1-888-MY-ETHER ext. 01781474
> 
> 
> 
> "Zee" wrote:
> 
> > Unfortunately I am not very familiar with VBA and therefore am not sure what 
> > to enter into the editor to accomplish this.
> > 
> > "galimi" wrote:
> > 
> > > Zee,
> > > 
> > > You could use VBA to cycle through all the sheets that are protected and 
> > > unprotect them that way.  Don't think it is possible without code.
> > > -- 
> > > http://www.ExcelHelp.us
> > > ed@ExcelHelp.us
> > > 888-MY-ETHER ext. 01781474
> > > 
> > > 
> > > 
> > > "Zee" wrote:
> > > 
> > > > Is it possible to unprotect all sheets in a workbook at the same time?
0
xdsouza (5)
11/26/2008 3:12:00 PM
If they have a password un comment the password section and change
mypassword for your password, the code goes in a standard module.

Sub Ulock_Sheets()
Dim Sh as worksheet
For each sh in sheets
sh.unprotect 'password:="mypassword"
next sh
end sub


-- 
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=33860

0
11/26/2008 3:49:26 PM
unlock is a reserved word.

Try...............

public sub un_lock()
Dim sht as Worksheet
  for each sht in thisworkbook.sheets
     sht.unprotect
  next
end sub

If the sheets have the same password you could use...........

Sub UnprotectAllSheets()
    Application.ScreenUpdating = False
    Dim N As Single
    For N = 1 To Sheets.Count
        Sheets(N).Unprotect Password:="password"  'edit to your actual pword
    Next N
    Application.ScreenUpdating = True
End Sub


Gord Dibben  MS Excel MVP

On Wed, 26 Nov 2008 07:12:00 -0800, Zee <xdsouza@sterlinghomesgroup.com>
wrote:

>Hi Galimi,
>
>I tried pasting the code into the VB editor and the first line states an 
>error.
>
>Thanks
>
>"galimi" wrote:
>
>> Zee,
>> 
>> The following code should do it:
>> 
>> public sub unlock
>>   for each sht in thisworkbook.sheets
>>      sht.unprotect
>>   next
>> end sub
>> -- 
>> http://www.ExcelHelp.us
>> ed@ExcelHelp.us
>> Call me at
>> 1-888-MY-ETHER ext. 01781474
>> 
>> 
>> 
>> "Zee" wrote:
>> 
>> > Unfortunately I am not very familiar with VBA and therefore am not sure what 
>> > to enter into the editor to accomplish this.
>> > 
>> > "galimi" wrote:
>> > 
>> > > Zee,
>> > > 
>> > > You could use VBA to cycle through all the sheets that are protected and 
>> > > unprotect them that way.  Don't think it is possible without code.
>> > > -- 
>> > > http://www.ExcelHelp.us
>> > > ed@ExcelHelp.us
>> > > 888-MY-ETHER ext. 01781474
>> > > 
>> > > 
>> > > 
>> > > "Zee" wrote:
>> > > 
>> > > > Is it possible to unprotect all sheets in a workbook at the same time?

0
Gord
11/26/2008 3:58:33 PM
Reply:

Similar Artilces:

Copy to specified sheet, values only
Good morning I hope someone can help me with this. I am currently using the following code to copy the entire data from one sheet, and paste it below the previous data in a different sheet: Set srcsht = Sheets("Working") Set dstsht = Sheets("All Trades") LastrowA = srcsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row LastrowB = dstsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1 srcsht.Range("A1:A" & LastrowA).EntireRow.Copy dstsht.Cells(LastrowB, 1) However, I need the macro to only paste the values, not the formulas, data...

Re-arranging table using pivot-table?
I have a worksheet with data organized somewhat like this: New York New York New York Albany Florida Miami Florida Orlando Florida Tampa Florida Jacksonville California Los Angeles California San Diego I need to convert this to a list of states and each city in the columns to the right of their corresponding state. Something like this: New York New York Albany Florida Miami Orlando Tampa Jacksonville California Los Angeles San Diego Any help is greatly appreci...

Re-install Outlook 2002
I am trying to re-install Outlook 2002 for my palm pilot after a crash. The installation will run until I get the message - "another version is already installed and must be uninstalled". The previous version was corrupt and I was unable to uninstall it. Now when I go into the control panel to add/remove the uninstall is no longer in the listed. Help! ...

Inserting new rows into sheet
I am using the following code to insert a new row onto my spreadsheet: Rows("5:5").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove This code will insert a new row above the current row number 5, which is okay. My problem is that the new row has the same height and possibly dimensions and formatting of row 4. Can I basically tell Excel to insert a row of cells that are a default size or a size of my specification?? I dont want Excel to insert a row of cells that are the same dimensions of the above or below row. I realize that I co...

Re-Enable CRM User
Hello Everyone, I am trying to enable a disabled CRM user and I received the following error message. Any thoughts or suggestions would be greatly appreciated. [COMException (0x80040204)] Microsoft.Crm.Platform.ComProxy.CBizUserClass.Enable(CUserAuth& Caller, String UserId) +0 Microsoft.Crm.Application.Platform.SystemUser.Activate(String id) +38 Microsoft.Crm.Web.BusinessManagement.SystemUserDetailPage.changeState(Object sender, DataEventArgs e) +127 Microsoft.Crm.Application.Forms.DataEventProcessor.Raise(FormEventId eventId, FormState state, User user, String objectId,...

navigational keys in protected sheets
I have two sheets in the same purchase order workbook. Second sheet is same as first with some additional cells locked. The navigational keys work fine when the sheets are unprotected. When I protect the two sheets, the keys continue to work fine on the first sheet. But on the second page the tab and left/right arrows will only work within the same row and will not go to next cell in next row. The Enter and up/down arrows do not work at all. The area that I'm working with is identical in both sheets. To input data on the second sheet, you have to keep using the mouse to go to ...

RE:Excel will not open from a web link
When opening a link on a web page that has an Excel Spreadsheet embedded in it, the spreadsheet will not open up Excel but opens within another browser as an Excel file. Programming on webpage is correct, opens up on other machines just fine. ...

Any way to show / hide sheets
I need a way, when i open a workbook, to hide all sheets till the user enter the correct password. When the user enter the password i want be able to show all sheets. Please, someone can help me? Thanks On May 6, 8:34=A0pm, SteAXA <Ste...@discussions.microsoft.com> wrote: > I need a way, when i open a workbook, to hide all sheets till the user en= ter > the correct password. > When the user enter the password i want be able to show all sheets. > Please, someone can help me? > Thanks I tried the following to make a sheet hidden Try same with some modificatio...

find sheet
I have a small marco that loads data from a file from another application into a workbook in Excel 2003. Sometimes the number of rows exceeds the 65,000+ cap so the remaining data spills over to a second sheet, sometimes not. I need a line or two that looks for sheet2: if it finds id it copies all the data from sheets 1 & 2. If there is no sheet 2, the procedure continues and it loads sheet 1. Nothing I try seems to work. I am a beginner with VBA. -- stan Stan, Have a look here: http://www.cpearson.com/excel/ImportBigFiles.aspx DB "stan" <sta...

Ron Bovey re: "Trim Selection" in ExcelUtilities
Cells in my Excel spreadsheet appear to be empty. However, when I use Edit/Go To/Special/Blanks, I get "No cells were found". In the past, Ron Bovey suggested going to the WWW.APPROS.COM website, download ExcelUtilities and then use the "Trim Selection" utility to remove the characters that are making the cells not empty. Since then, I've gone to the WWW.APPROS.COM website and downloaded the ASAP Utilities. However, I can seem to find the "Trim Selection" nor any utility that will do the same thing. Help! Try going to Rob (not Ron) Bovey's site: ...

Copying part of a work sheet.
I am probably missing something but I have a long 'list' in a work sheet. I want to copy it to another worksheet. More importatly I want tp present it on my web-site but have yet to find a way of copying only the rows I want on the site, not the other 400 rows underneath, rather like selecting what you want to print . ...

Thanks Ken Wright but one more question RE saving data from circular references
Thanks heaps for that macro Ken Wright . I have run it and it works. However it loops forever. Can I set it up so that it could save dat from a certain number of iterations? Thanks again Michae -- Message posted from http://www.ExcelForum.com Hi Michael, If you stayed within your thread and did not change the subject Ken would see your reply as a reply to his reply and would see it a lot faster than when you start a new thread. Ken would probably see your post highlighted in RED. Also someone other than Ken might have been able to help you. (not me I avoid anything that hints of circul...

Re: exmerge error
I am getting the following error when trying to use exmerg can anyone please help *************************************************************** Microsoft Exchange Mailbox Merge Program, v4.00.02 Start Logging:February 27, 2004 20:04:3 *************************************************************** [20:04:31] Logging Level: Non [20:04:31] Reading settings from file 'C:\Program Files\Exchsrvr\BIN\EXMERGE.INI' [20:04:32] Error 8007203a opening an LDAP connection. ('LDAP://EXCHANGE/rootDSE') (CADRoutines::GetNamingContextData [20:04:32] Accessing Domain Controller 'PROFI...

How do I get invisible lines & borders to re-appear?
In Publisher 2003, table grid lines, text box borders and autoshape borders have become invisible on-screen, although they do print out. Any ideas on how to remedy this? Recently installed Adobe PDF Maker which seems to have precipitated this, although it could be entirely co-incidental. Try updating your video drivers. -- JoAnn Paules MVP Microsoft [Publisher] "Ackerman & Co" <Ackerman & Co@discussions.microsoft.com> wrote in message news:0FFE40C2-D6A8-43AE-89E4-061EBB6501DB@microsoft.com... > In Publisher 2003, table grid lines, text box borders and auto...

Transfer Spreadsheet method Not replacing Sheet Name
I have this line of code which Transfers one record of a query and make a Worksheet named A_EXPORT. This worked fine until I started havin some linked references in the workbook to this newly created shee A_EXPORT Now instead of replacing the sheet every time I transfer data fro Access to Excel it is adding a sheet named A_EXPORT1 etc How can I get this to replace the sheet or can I copy the recordset t row 2 of A_EXPORT instead? EXTREMELY IMPORTANT!!!!!! DoCmd.TransferSpreadsheet acExport, 8, "A_EXPORT", str, True, "" FollowHyperlink st -- Message posted from http://ww...

re re re ????
nefertiti ??? ...

Save a page to a specific sheet
Hi, I have to download a certain file in excel which i have to save into another excel file. Is it possible to save this file into a named sheet, without losing all the other sheets which i am using. -- carkoe ------------------------------------------------------------------------ carkoe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29740 View this thread: http://www.excelforum.com/showthread.php?threadid=494554 One way to do this is to open both workbooks, select the sheet(s) you need from the source workbook, right click on the selected tabs, click ...

Re:
...

Re: Where should be the best place to stored Applcation Data Files?
"Giovanni Dicanio" <gdicanio@_NOSPAM_email_DOT_it> ha scritto nel messaggio news:... > BTW: this post by John Robbins is very interesting reading: > http://www.wintellect.com/CS/blogs/jrobbins/default.aspx copy-and-pasto - the correct link is this: http://www.wintellect.com/CS/blogs/jrobbins/archive/2008/07/10/in-defense-of-vista-and-the-challenges-facing-windows-7.aspx Giovanni And then there's this Vista security discussion from last Friday (cool date, 8/8/8): http://it.slashdot.org/article.pl?sid=08/08/08/1155208 -GB On Fri, 8 Aug 2008 18:50:23 +0200, ...

Re-enter payee information
I have been using MS Money to pay bills and frequent annoyance is when I type in the payee, the selection list never shows the payees. I type it in and have to re- enter the account number and everything. Doesn't make sense that i have to keep putting this in. Anyone know what I am doing wrong? I've checked these boards and found help with my passport problem but no entries on this issue. Money has two kinds of Payees. Explicit--those on the explicit list that shows in Accounts & Bills|Categories & Payees|Payees--and implicit--those that are recorded somewhere in a tra...

excel multilple sheets
Need to branch to another sheet in the workbook and then return to the sheet that the branch was started from! Now for the kicker The user can change the name of the sheet that the program runs from!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Chip Pearson just posted some code (you wanted code???) that works very nicely. http://groups.google.com/groups?threadm=utf6mffiDHA.620%40TK2MSFTNGP11.phx.gbl It works if the sheet has been renamed, too. Arr wrote:...

automatic tranfer of data from worksheet to time sheets
i use excell to both keep reports and i would like to have data such as time/dates from the reports transfered to a separtate worksheet to track time spent on different projects ...

Re: Outlook locks up with a officelifeboathang
"Andy Lay" <58746@gmail.com> wrote in message news:... > Message-ID: <a7b660e6180d48ddae1e1df6c5775272@newspe.com> > X-Mailer: http://www.umailcampaign.com, ip log:72.24.97.26 > Newsgroups: microsoft.public.outlook.general > NNTP-Posting-Host: 22.bb.5446.static.theplanet.com 70.84.187.34 > Path: TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl!newspe.com > Lines: 1 > Xref: TK2MSFTNGP01.phx.gbl microsoft.public.outlook.general:737044 > > Hi Swinglower- > > I have luck as you do :) A Chief Exec machine with this very issue....

Re: Share sub-folder in Inbox
I already grant the permission to right person and add secondary mailbox to the exchange account, in his outlook folder list, he can see the shared mailbox, but only mailbox not the sub-folder, if we share those sub-folders individually, then we can view under shared mailbox, but this shouldn't be the way. we want it sub-folder can be viewed and not be set one by one. any idea? thanks Henry "Sue Mosher [MVP-Outlook]" <suemvp@outlookcode.com> wrote in message news:%237hESO3PFHA.508@TK2MSFTNGP12.phx.gbl... Either you haven't completed the process of granting permission...

RE: Apply this critical update from M$
--wmhrdizzoa Content-Type: multipart/related; boundary="axyeonzy"; type="multipart/alternative" --axyeonzy Content-Type: multipart/alternative; boundary="innmlsikefhcqjc" --innmlsikefhcqjc Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "September 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to continue keep...