Unprotecting multiple worksheets w/ Macro

I am protecting with a macro, multiple worksheets in a workbook with a
macro.  I can't get the unprotect of multiple worksheets to work.
Does anyone have a working macro to do this.


_____________

Tippy
0
tippy (17)
10/25/2003 10:47:06 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
461 Views

Similar Articles

[PageSpeed] 48

Sub UnprotectSheets()
Dim sh as Worksheets
for each sh in ThisWorkbook.Worksheets
   if sh.ProtectContents or sh.ProtectScenarios or _
    sh.ProtectDrawingObjects then
         sh.unprotect Password:="ABCD"
   end if
Next
End Sub

--
Regards,
Tom Ogilvy

<tippy@att.net> wrote in message
news:c7vlpv0sqb9eov641aj31ohnqnps7hvgvm@4ax.com...
> I am protecting with a macro, multiple worksheets in a workbook with a
> macro.  I can't get the unprotect of multiple worksheets to work.
> Does anyone have a working macro to do this.
>
>
> _____________
>
> Tippy


0
twogilvy (1078)
10/25/2003 11:39:09 PM
one way:

    Public Sub UnProtectMultipleSheets()
        Const cPword As String = "drowssap"
        Dim oWkSht As Worksheet
        For Each oWkSht In Worksheets
            oWkSht.Unprotect cPword
        Next oWkSht
    End Sub


In article <c7vlpv0sqb9eov641aj31ohnqnps7hvgvm@4ax.com>,
 tippy@att.net wrote:

> I am protecting with a macro, multiple worksheets in a workbook with a
> macro.  I can't get the unprotect of multiple worksheets to work.
> Does anyone have a working macro to do this.
> 
> 
> _____________
> 
> Tippy
0
jemcgimpsey (6723)
10/25/2003 11:39:50 PM
On Sat, 25 Oct 2003 17:39:50 -0600, "J.E. McGimpsey"
<jemcgimpsey@mvps.org> wrote:

>one way:
>
>    Public Sub UnProtectMultipleSheets()
>        Const cPword As String = "drowssap"
>        Dim oWkSht As Worksheet
>        For Each oWkSht In Worksheets
>            oWkSht.Unprotect cPword
>        Next oWkSht
>    End Sub


I was using something very similar trying to use something you had
posted on protecting multiple spreadsheets.

It seems the lower case "c" in fron of Pword" and the "o" in fron of
Wksht are the major differences.  Also I think I had PWord = Password.
I'll give it a shot.  Thanks.  


>
>In article <c7vlpv0sqb9eov641aj31ohnqnps7hvgvm@4ax.com>,
> tippy@att.net wrote:
>
>> I am protecting with a macro, multiple worksheets in a workbook with a
>> macro.  I can't get the unprotect of multiple worksheets to work.
>> Does anyone have a working macro to do this.
>> 
>> 
>> _____________
>> 
>> Tippy

_____________

Tippy
0
tippy (17)
10/26/2003 5:11:58 AM
    Public Sub UnProtectMultipleSheets()
        Const Pword As String = "drowssap"
        Dim WkSht As Worksheet
        For Each WkSht In Worksheets
            WkSht.Unprotect Pword
        Next WkSht
    End Sub

Would work just as well.  Yes you would need to supply the actual password.

--
Regards,
Tom Ogilvy


<tippy@att.net> wrote in message
news:rllmpv02rct2ipt4sv53qfus1q969t8pu5@4ax.com...
> On Sat, 25 Oct 2003 17:39:50 -0600, "J.E. McGimpsey"
> <jemcgimpsey@mvps.org> wrote:
>
> >one way:
> >
> >    Public Sub UnProtectMultipleSheets()
> >        Const cPword As String = "drowssap"
> >        Dim oWkSht As Worksheet
> >        For Each oWkSht In Worksheets
> >            oWkSht.Unprotect cPword
> >        Next oWkSht
> >    End Sub
>
>
> I was using something very similar trying to use something you had
> posted on protecting multiple spreadsheets.
>
> It seems the lower case "c" in fron of Pword" and the "o" in fron of
> Wksht are the major differences.  Also I think I had PWord = Password.
> I'll give it a shot.  Thanks.
>
>
> >
> >In article <c7vlpv0sqb9eov641aj31ohnqnps7hvgvm@4ax.com>,
> > tippy@att.net wrote:
> >
> >> I am protecting with a macro, multiple worksheets in a workbook with a
> >> macro.  I can't get the unprotect of multiple worksheets to work.
> >> Does anyone have a working macro to do this.
> >>
> >>
> >> _____________
> >>
> >> Tippy
>
> _____________
>
> Tippy


0
twogilvy (1078)
10/26/2003 2:28:21 PM
The c and the o are only mnemonics (for constant and object) that 
were specified for variables in the code I lifted the procedure from 
and have no effect on the operation of the macro. This would work 
just as well:

    Public Sub Larry()
        Const Curly As String = "drowssap"
        Dim Moe As Worksheet
        For Each Moe In Worksheets
            Moe.Unprotect Password:=Curly
        Next Moe
    End Sub


In article <rllmpv02rct2ipt4sv53qfus1q969t8pu5@4ax.com>,
 tippy@att.net wrote:

> It seems the lower case "c" in fron of Pword" and the "o" in fron of
> Wksht are the major differences.  Also I think I had PWord = Password.
> I'll give it a shot.  Thanks.
0
jemcgimpsey (6723)
10/26/2003 3:00:41 PM
The macro below still gives me an error.

Sub UnProtectMultipleSheets()
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet
For Each wkSht In Worksheets
wkSht.Unprotect PWORD
Next wkSht
End Sub



Although I protect the test worksheets with "drowssap" using a macro,
the unprotect above does not work.  I get run time error 1004 and the
message that the password is not correct.  I checked the spelling and
case, no luck.

Here is the macro, which works well, that I used to protect the
worksheets.

Public Sub ProtectAll()
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet
For Each wkSht In Worksheets
wkSht.Protect PASSWORD:=PWORD
Next wkSht
End Sub














On Sun, 26 Oct 2003 08:00:41 -0700, "J.E. McGimpsey"
<jemcgimpsey@mvps.org> wrote:

>The c and the o are only mnemonics (for constant and object) that 
>were specified for variables in the code I lifted the procedure from 
>and have no effect on the operation of the macro. This would work 
>just as well:
>
>    Public Sub Larry()
>        Const Curly As String = "drowssap"
>        Dim Moe As Worksheet
>        For Each Moe In Worksheets
>            Moe.Unprotect Password:=Curly
>        Next Moe
>    End Sub
>
>
>In article <rllmpv02rct2ipt4sv53qfus1q969t8pu5@4ax.com>,
> tippy@att.net wrote:
>
>> It seems the lower case "c" in fron of Pword" and the "o" in fron of
>> Wksht are the major differences.  Also I think I had PWord = Password.
>> I'll give it a shot.  Thanks.

_____________

Tippy
0
tippy (17)
10/28/2003 3:57:40 AM
I just pasted both routines into a workbook and they both work fine. 
The only way I can get the Unprotect to fail is if the protected 
book is not active.

Hmm...


In article <f7qrpvcgsn3vufsts7tbqo2q9d3s2o9kbq@4ax.com>,
 tippy@att.net wrote:

> The macro below still gives me an error.
> 
> Sub UnProtectMultipleSheets()
> Const PWORD As String = "drowssap"
> Dim wkSht As Worksheet
> For Each wkSht In Worksheets
> wkSht.Unprotect PWORD
> Next wkSht
> End Sub
> 
> 
> 
> Although I protect the test worksheets with "drowssap" using a macro,
> the unprotect above does not work.  I get run time error 1004 and the
> message that the password is not correct.  I checked the spelling and
> case, no luck.
> 
> Here is the macro, which works well, that I used to protect the
> worksheets.
> 
> Public Sub ProtectAll()
> Const PWORD As String = "drowssap"
> Dim wkSht As Worksheet
> For Each wkSht In Worksheets
> wkSht.Protect PASSWORD:=PWORD
> Next wkSht
> End Sub
0
jemcgimpsey (6723)
10/28/2003 4:32:50 AM
I don't understand.  It is "active" when the first macro works, stored
in module 1.  The 2nd module contains the unprotect macro.  If the
first macro works during a session, why wouldn't the 2nd?  I use
shortcut keys Ctrl-p and Ctrl-u, which should have no bearing.  

Hmmm -- I noticed your specific reference to "protected book," and I'm
referring to protected "worksheets" in a single workbook, but I
suspect you mean the same thing.  

Maybe there is something more basic that I haven't considered....

On Mon, 27 Oct 2003 21:32:50 -0700, "J.E. McGimpsey"
<jemcgimpsey@mvps.org> wrote:

>I just pasted both routines into a workbook and they both work fine. 
>The only way I can get the Unprotect to fail is if the protected 
>book is not active.
>
>Hmm...
>
>
>In article <f7qrpvcgsn3vufsts7tbqo2q9d3s2o9kbq@4ax.com>,
> tippy@att.net wrote:
>
>> The macro below still gives me an error.
>> 
>> Sub UnProtectMultipleSheets()
>> Const PWORD As String = "drowssap"
>> Dim wkSht As Worksheet
>> For Each wkSht In Worksheets
>> wkSht.Unprotect PWORD
>> Next wkSht
>> End Sub
>> 
>> 
>> 
>> Although I protect the test worksheets with "drowssap" using a macro,
>> the unprotect above does not work.  I get run time error 1004 and the
>> message that the password is not correct.  I checked the spelling and
>> case, no luck.
>> 
>> Here is the macro, which works well, that I used to protect the
>> worksheets.
>> 
>> Public Sub ProtectAll()
>> Const PWORD As String = "drowssap"
>> Dim wkSht As Worksheet
>> For Each wkSht In Worksheets
>> wkSht.Protect PASSWORD:=PWORD
>> Next wkSht
>> End Sub

_____________

Tippy
0
tippy (17)
10/28/2003 5:25:52 AM
Is it possible that you've protected one or more sheets with a 
different password before you run the ProtectAll macro?

Running ProtectAll on a protected sheet won't change it's original 
password, and will give you the run-time error when you try to 
unprotect, since you're using the wrong password.


In article <5vurpvki8jnvfao71qlpt8elj4ksgkunh5@4ax.com>,
 tippy@att.net wrote:

> Maybe there is something more basic that I haven't considered....
0
jemcgimpsey (6723)
10/28/2003 1:24:30 PM
Nope, all protected with the same macro.  I am stumped.  


On Tue, 28 Oct 2003 06:24:30 -0700, "J.E. McGimpsey"
<jemcgimpsey@mvps.org> wrote:

>Is it possible that you've protected one or more sheets with a 
>different password before you run the ProtectAll macro?
>
>Running ProtectAll on a protected sheet won't change it's original 
>password, and will give you the run-time error when you try to 
>unprotect, since you're using the wrong password.
>
>
>In article <5vurpvki8jnvfao71qlpt8elj4ksgkunh5@4ax.com>,
> tippy@att.net wrote:
>
>> Maybe there is something more basic that I haven't considered....

_____________

Tippy
0
tippy (17)
10/29/2003 2:37:11 AM
Reply:

Similar Artilces:

Sooo frusterated w/ Outlook & Hotmail- Pls help!
I've been having problems receiving my hotmail email in Outlook for about the last month. When I first got my new computer in March, I set up Outlook w/ the Hotmail Outlook Connector and had NO problems. Recently I started receiving completely blank emails- no subject, to, from, empty body, etc. If I accessed my hotmail online the message appeared perfectly. I searched for solutions but nothing I found worked. I tried the new beta outlook connector to no avail. In fact, that made matters worse as outlook gave me an error message & I could no longer open the folders ...

How to code macro to disconnect internet connection?
Does anyone have any suggestions on how to code macro to disconnect internet connect for macro? I would like to add a code at the end of macro to disconnect internet connection, does anyone have any suggestions on how to do it in Excel? Thanks in advance for any suggestions Eric How are you making the internet connection? do yo mean to close a web browser, shutdown a dialup connection, kill an ethernet connection. Yo need to be a little bit more specific. I program in C# and open and close connections all the time. I need to know a little bit about the connection type to he...

Worksheet Protection Problem
I have Excell 2003 (11.5612.5606). My worksheet is protected (tools+protection+protect worksheet), and fo the most part, the protection seems to be doing its thing okay. When go to protect the worksheet, the only thing I have checked to allow th user to do is "select unlocked cells"; the "format cells" is NO checked. I have used the format+cells+protection to lock only the cell I want protected. The problem is when the user uses "CTRL C" & "CTRL V" to copy and past data from one cell to another, it also copies the cell's format (???) As a re...

find formula between worksheets
Worksheet 2 is a calendar with cell A1 labeled Oct. 2 , B1 labeled Oct.3, and so on. Worksheet 1 is a chart with column A labeled clients and column B labeled with the dates that someone met with the client. In worksheet2 I would like a formula that would pull Client names from column A on worksheet 1 (dates are in column b) into the calendar on worksheet2 in cell A2, for Oct. 2, and B2, Oct.3. For example if on worksheet1 if cell A1 said Kerry and cell B1 said Oct. 3, I would want a formula that would pull the client name Kerry into worksheet2 on cell B2 under the heading in cell B2 (...

Simple macro please
All I want is to have a macro from one worksheet to another for a report, I have done them before but my brain has seized up, due to this seizure please can I have simple step by step instructions. It would help if we were told what the macro was to do. ...from one worksheet to another ... is a tad lacking in detail. -- HTH Bob Phillips "sasha" <sasha@discussions.microsoft.com> wrote in message news:4449168A-DD3D-4965-A29F-ABD674EBDFD5@microsoft.com... > All I want is to have a macro from one worksheet to another for a report, I > have done them before but my brai...

Excel OLE data in PPT reverting to OLD data after macro updation
Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) ...

How can I have multiple fonts in a legend?
I need to have part of my legend be in italics and part in arial. In the source code it is correct, but in the chart it is all in arial. I tried inserting a texbox, but it doesn't line up exactly. Any suggestions? Hi, The font for the legend can not be formatted for specific entries or parts of an entry. Textboxes or data labels with the legend key displayed are the normal work around for fancy legends. Can you explain further the 'line up' problem you have with text boxes? Cheers Andy Becky wrote: > I need to have part of my legend be in italics and part in arial....

Email a single Worksheet
Hello from Steved Is it possible to email a single worksheet with all the formulas I am using excel 2000 Thankyou. Hi see: http://www.rondebruin.nl/sendmail.htm -- Regards Frank Kabel Frankfurt, Germany "Steved" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:26eb01c47064$444660c0$a401280a@phx.gbl... > Hello from Steved > > Is it possible to email a single worksheet with all the > formulas I am using excel 2000 > > Thankyou. Thankyou. >-----Original Message----- >Hi >see: >http://www.rondebruin.nl/sendmail.htm > >-- ...

Stopping a macro based Setwarning diag box
I have created a Macro that includes a spreadsheet transfer and append. My SetWarnings is Yes. However, I noticed that when I get the dialog box indicating what I am about to do and ask if I am sure I want to run this query, I select no but the macro continues anyway. How can I get the Macro to stop running? Answered in your post 30 minutes later. Hi AB1, Sounds to me like it's time to make the move away from macros to VBA code. This is very easy to do with a little VBA code. The same thing applies for your follow-on question "SetWarning in macro". Perhaps ...

November 10, 2009 Security Update macro problems
After the Security Update for Excel 2003 and 2007 from November 10, 2009, macros became much slower and a refresh problem started happening. What happens is that when, through VBA, I place a value on a cell on an inactive sheet, the cell where the value has been placed is seen in the active sheet. This is causing some unusual behavior, as i place a lot of values in sheets that are not active. Does anyone have a problem like this? http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.officeupdate&tid=c67c9a0d-4c83-41ec-b4c6-778974190066&cat=&a...

Need Help With Macro to set a value in new sheets
Hey everybody. I am using a variant of Dave Petersen's macro code to create a new sheet and rename that sheet based on a list of values in Sheet_2. Here is what I have so far: Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col C in a sheet: Sheet_2 ' Sub will copy sheets based on the sheet named as: Sheet1 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim ListRng2 As Range Dim myCell2 As Range Set TemplateWks = Worksheets("Sheet1"...

Merge multiple records in the same table in access
Hello I am dealing with some unfriendly import files which import as: timestamp position name 001 2 Jon 001 3 Bob 001 1 Ann 001 4 Mike 002 1 Joe 002 2 Sue 003 1 Jeff 004 5 James 004 1 Andy 004 2 Beth 004 4 Mitch 004 3 Chris And would like to create a new table that displays thusly: timestamp position1 ...

Sorting of worksheet, half of columns are formulas referring to 1st worksheet...
I want to sort my worksheet alphabetically by last name. Half of my worksheet columns contain contact information that are formulas referring to another worksheet in the file. The other half is regular values of dates in different columns. (We use the file to track correspondence). I added 100 more rows to the worksheet.(about 300 total). Now I need to sort, but everything I try results in the rows getting switched around. Ex: Dr. Smith's dates aren't correct. They're on Dr. Smyth's row. Dr. Smith has Dr. Snyder's dates. Any ideas? Thanks. Angela -------------...

Send a worksheet to an open email
Hi Forum, Is there a way of sending an Excel sheet as an attachment to an email in Outlook that is already open. I have read through the forum and all replies seem to open a new email and then make the attachment, I need to do it the other way around if possible. Many thanks in advance. Graham If you have already looked at Ron's site, then I don't know where else to look. http://www.rondebruin.nl/sendmail.htm "Graham F" <GrahamF@discussions.microsoft.com> wrote in message news:CF8577CF-D580-40EE-B85D-33E01808E408@microsoft.com... > Hi Forum, ...

Sharing Contacts w/ a team and CRM Outlook Client
In order for a contact to sync w/ Outlook in CRM it has to be owned by a the user or shared to the user. What about when sharing the contact w/ a team, and the Team members go offline the contacts do not sync. I am under the impression they are suppose to sync w/ the user who is a member of that team! Jimmy -- James M. Crews, MCSE, MCP+I Crewsen.com LLC, President jcrews@crewsen.com 901-726-0731 ** For urgent short text messages, email me at jimmycell@crewsen.com ***For technical support, please email support@crewsen.com or call 726-0731 ext. 3 Hi Jimmy, Team shared contacts do not ...

Help Needed: VBA code to copy a worksheet into a new workbook
Hi All, I am trying to write VBA code that accomplishes the following: 1) Copy Sheet1 from workbook1 into a new workbook. 2) Open the "Save as" dialog box that allows the user to name and save the new workbook. 3)Return the user to workbook1. My problem is I don't know how to open the "Save as" dialog box. Thanks in advance Bob Bob Application.Dialogs(xlDialogSaveAs).Show Regards Trevor "Kenwalt" <Bobkennedy@hotmail.com> wrote in message news:1111170592.071328.140780@f14g2000cwb.googlegroups.com... > Hi All, > > I am trying to writ...

Multiple dialogs in a view
Hi, VCC+ 6.0 I desire to create a small dialog and then populate a view with an array of this dialog. If array of dialogs extended outside of the view, I wanted scroll bars to appear. I thought that CFormView with a splitter window was somehow the way to go on this but I am having trouble. Any one know the most efficient way to accomplish this. I'm just looking for a general direction, not any details, I can dig that up. Thanks so much in advance. On Mon, 30 Jan 2006 10:59:42 -0500, Bob wrote: > Hi, > > VCC+ 6.0 > > I desire to create a small dialog and then ...

Validating XML w/o Namespace against XSD
I am trying to validate an XML file that does not contain any xmlns: namespace attribute against a predefined XSD schema. I should think this would be easy to do, since there must be far more "unqualified" XML files out there than there are ones that include a namespace reference. Unfortunately, as I am coming to hate about all things XML, the easy things are extraordinarily difficult and/or subtle. I know how to create a schema collection dynamically and validate qualified XML files against it. But I can't figure out the "no namespace in the XML file" situation (oth...

coping worksheets with hyperlinks
Hello, I am build a budget template which will have sever worksheets within the workbook and each worksheet will be identical. The problem is that I have hyperlink to directed toward different part of the template and every time I create a new template by coping the skeleton template the hyperlinks direct back to the skeleton template does anybody have any suggests -- Andrew Jackson Protect your hyperlink targets: =HYPERLINK("#'Sheet3'!B9","temp") Excel will not change the text between the double quotes when the workbook or worksheet is copied. -- Gary'...

How to ID calling worksheet if it isn't the active sheet?
I have a udf that uses .ActiveSheet to identify the sheet calling it. Works great as long as the sheet doing the calculating is the active one. This sheet with that udf has now been replicated - various what if version copies. Each of these copies comes along with its own copy of that udf in its cells. Question is, how does the udf ID the calling sheet if it's not the currently active one? There are global controls that change input values used by these copied sheets. All must recalculate using this global value and their own local variables. Application.Caller will b...

Null value in multiple condition formula
Hi, I wonder if anybody out there could tell me how to do this? I am wanting to count the number of times in my worksheet a cell in column N is empty, WHEN the value of column C is "C" AND the value of column H (which contains dd/mm/yyyy data) is within the month of January. I am using the formula: =SUMPRODUCT((C2:C100="C")*(MONTH(H2:H100)=1)) to count those cells where Column C = "C" AND the month in column H = Jan (thanks to you wise and helpful people on the forum, I might add) but cannot work out how to do the null value in column N bit! Any help w...

Compiling from command line with macros
Hey, I am building a system that would build our product from command line on a regular basis. Calling msdev from command line is really easy, but I am now having trouble incrementing the version numbers of each build. I followed this KB article http://support.microsoft.com:80/support/kb/articles/Q237/8/70.ASP to make a VBScript macro that increments the version information on each succesful build. It works like a charm when built from VC++ IDE. The problem is to make it apply to command line builds as well. So the question is, can I compile a workspace from command line so that it would be...

How Do I Make a Summary Page w/in Spreadsheet?
Example: Spreadsheet has a page for each year (2000, 2001, 2002, 2003, 2004) - each page contains same # of columns and same titles. How do I take all the pages and make one ongoing summary page of all data - one which updates from the year page? Is this what a pivot sheet does? TYIA Pls do NOT multi post cuz those that would respond generally read all. -- Don Guillett SalesAid Software donaldb@281.com "Joanne" <anonymous@discussions.microsoft.com> wrote in message news:94a901c4d167$fa031840$a301280a@phx.gbl... > Example: Spreadsheet has a page for each year (200...

accessing outlook data using excel macro
Hey all, I'm very new with all of this, however, my problem is this: I have a bunch of data in excel which I then want to compare to certain "task" fields in outlook (ie. the "Due Date" column in Tasks for example), then update those outlook fields with new data. I was able to do it in an Outlook macro, but not in the Excel macro. I get a "User defined type not defined" error when trying to define a namespace variable as "Outlook.Namespace" Any help would be greatly appreciated. Thanks. ...

Multiple prices on labels?
Hi. I am wondering if there is a way to put multiple prices on the shelf labels. I would like to be able to print a label with the regular price, and price level A on the same label as an example. You can design a label to include pretty much whatever you want. Go into Utilities>Label Designer. It's quite primitive if you're used to WYSIWYG design software, but I have been able to come up with working label formats through trial and error. You should not expect the printed label to look all that much like the design on the screen. But by tweaking the design and printing out ...