Generic protect/unprotect code through buttons and code?

I looked through the archives but have seen that there can be problems with
protecting/unprotecting sheets with vb so thought I would just ask about
this one. I have buttons on some of my sheets. I'm ready to protect the
sheets whereas up till now they remained unprotected. Apparently, we can
unprotect sheet, run code, then protect sheet again. (I'm assuming buttons
will still be clickable even though protected??)

Also, we'll need a generic line of coding for protecting/unprotecting -
i.e., without any sheet names and/or passwords - as that would be easiest to
deal with as then I could simply copy those lines to all the current
workbooks. It would mean adding a simple unprotect line after title of
script and have code then execute rest of script - such as sort, etc. - and
then protect the sheet before script finishes.

Also, people have posted about problems with passwords so we won't need any
passwords. And the other problem I saw is that code might not work if sheet
protected manually, etc. So it was recommended in that particular post to
use an IF statement. I haven't been able to find an example of code that
will do these 2 things so begging indulgence of group to help with this one.

This is final step in completing all the workbooks I made here at this job 1
1/2 yrs ago. They've been unprotected all this time. Hopefully I can close
them up yet still retain full functionality with all the buttons/code! <g>

Thank you!! :oD

>Apparently, we can unprotect sheet, run code, then protect sheet again.
Yes.

> (I'm assuming buttons will still be clickable even though protected??)
Yes. You may need to change the objects protection if it is locked. Try it
to find out.

>we'll need a generic line of coding for protecting/unprotecting -
> i.e., without any sheet names and/or passwords
Use Activesheet.

>And the other problem I saw is that code might not work if sheet
> protected manually, etc.
I've never had that problem.... which does not mean that it is not a
problem.

I haven't been able to find an example of code that
> will do these 2 things so begging indulgence of group to help with this
one.

ActiveSheet.Unprotect 'place at the beginning of the code
ActiveSheet.Protect 'place in the exit routine so it is executed even
if the error routine is entered

--
My handle should tell you enough about me. I am not an MVP, expert, guru,
etc. but I do like to help.


"StargateFanFromWork" <[email protected]> wrote in message
news:[email protected]...
> I looked through the archives but have seen that there can be problems
with
> protecting/unprotecting sheets with vb so thought I would just ask about
> this one. I have buttons on some of my sheets. I'm ready to protect the
> sheets whereas up till now they remained unprotected. Apparently, we can
> unprotect sheet, run code, then protect sheet again. (I'm assuming
buttons
> will still be clickable even though protected??)
>
> Also, we'll need a generic line of coding for protecting/unprotecting -
> i.e., without any sheet names and/or passwords - as that would be easiest
to
> deal with as then I could simply copy those lines to all the current
> workbooks. It would mean adding a simple unprotect line after title of
> script and have code then execute rest of script - such as sort, etc. -
and
> then protect the sheet before script finishes.
>
> Also, people have posted about problems with passwords so we won't need
any
> passwords. And the other problem I saw is that code might not work if
sheet
> protected manually, etc. So it was recommended in that particular post to
> use an IF statement. I haven't been able to find an example of code that
> will do these 2 things so begging indulgence of group to help with this
one.
>
> This is final step in completing all the workbooks I made here at this job
1
> 1/2 yrs ago. They've been unprotected all this time. Hopefully I can
close
> them up yet still retain full functionality with all the buttons/code!
<g>
>
> Thank you!! :oD
>
>

StargateFanFromWork wrote:
> I looked through the archives but have seen that there can be problems with
> protecting/unprotecting sheets with vb so thought I would just ask about
> this one. I have buttons on some of my sheets. I'm ready to protect the
> sheets whereas up till now they remained unprotected. Apparently, we can
> unprotect sheet, run code, then protect sheet again. (I'm assuming buttons
> will still be clickable even though protected??)
>
> Also, we'll need a generic line of coding for protecting/unprotecting -
> i.e., without any sheet names and/or passwords - as that would be easiest to
> deal with as then I could simply copy those lines to all the current
> workbooks. It would mean adding a simple unprotect line after title of
> script and have code then execute rest of script - such as sort, etc. - and
> then protect the sheet before script finishes.
>
> Also, people have posted about problems with passwords so we won't need any
> passwords. And the other problem I saw is that code might not work if sheet
> protected manually, etc. So it was recommended in that particular post to
> use an IF statement. I haven't been able to find an example of code that
> will do these 2 things so begging indulgence of group to help with this one.
>
> This is final step in completing all the workbooks I made here at this job 1
> 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close
> them up yet still retain full functionality with all the buttons/code! <g>
>
> Thank you!! :oD

Have only been working with VBA for less than a year, this time (did a
little years ago, but not much). But I have been working with password
protected sheets for almost that long with no problems whatsoever. I've
just coded the macros to unprotect and the protect, using the
appropriate password.
Hope this helps in your world.

StargateFanFromWork wrote:
> I looked through the archives but have seen that there can be problems with
> protecting/unprotecting sheets with vb so thought I would just ask about
> this one. I have buttons on some of my sheets. I'm ready to protect the
> sheets whereas up till now they remained unprotected. Apparently, we can
> unprotect sheet, run code, then protect sheet again. (I'm assuming buttons
> will still be clickable even though protected??)
>
> Also, we'll need a generic line of coding for protecting/unprotecting -
> i.e., without any sheet names and/or passwords - as that would be easiest to
> deal with as then I could simply copy those lines to all the current
> workbooks. It would mean adding a simple unprotect line after title of
> script and have code then execute rest of script - such as sort, etc. - and
> then protect the sheet before script finishes.
>
> Also, people have posted about problems with passwords so we won't need any
> passwords. And the other problem I saw is that code might not work if sheet
> protected manually, etc. So it was recommended in that particular post to
> use an IF statement. I haven't been able to find an example of code that
> will do these 2 things so begging indulgence of group to help with this one.
>
> This is final step in completing all the workbooks I made here at this job 1
> 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close
> them up yet still retain full functionality with all the buttons/code! <g>
>
> Thank you!! :oD


I've been using this for some time with good success. I have the main
macro assigned to a custom button on my main toolbar, and it works as a
toggle to Protect or Unprotect, as I require. The "unprotect" routine
works regardless of the Protection password used (if any).

Sub ProtectionToggle()
Application.ScreenUpdating = False

If ActiveSheet.ProtectContents = True Then
' Unprotect -- a valid command by itself for non-password
protected sheets
ThisSheetPassword
Else
Protect
End If

Application.ScreenUpdating = True
End Sub

******************************************

Sub ThisSheetPassword()
' Obtained from pcc at MrExcel.com in response to my query
' about updated PASSWORDS.XLA (to unprotect sheets protected by
' an unknown password).

On Error Resume Next
ActiveSheet.Protect "", , , , True
ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")

End Sub

******************************************

And if I have a workbook full of protected sheets, and want to
unprotect all of them for mass edits or whatever reason, then I use
this one:

Sub SheetPassword()
' Obtained from pcc at MrExcel.com in response to my query
' about updated PASSWORDS.XLA (to unprotect sheets protected by
' an unknown password).

StartSheet = ActiveSheet.Name
On Error Resume Next
For myCounter = 1 To Worksheets.Count
Worksheets(myCounter).Select
ActiveSheet.Protect "", , , , True
ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
Next myCounter

Sheets(StartSheet).Select
End Sub

On Fri, 30 Dec 2005 14:13:48 -0600, "AnExpertNovice"
<j@The~N_o~S_p_a_m~PostOffice.com> wrote:

>>Apparently, we can unprotect sheet, run code, then protect sheet again.
>Yes.
>
>> (I'm assuming buttons will still be clickable even though protected??)
>Yes. You may need to change the objects protection if it is locked. Try it
>to find out.
>
>>we'll need a generic line of coding for protecting/unprotecting -
>> i.e., without any sheet names and/or passwords
>Use Activesheet.
>
>>And the other problem I saw is that code might not work if sheet
>> protected manually, etc.
>I've never had that problem.... which does not mean that it is not a
>problem.
>
>I haven't been able to find an example of code that
>> will do these 2 things so begging indulgence of group to help with this
>one.
>
>ActiveSheet.Unprotect 'place at the beginning of the code
>ActiveSheet.Protect 'place in the exit routine so it is executed even
>if the error routine is entered

<g> Okay, well, that was darned easy!! I don't know why I had such
trouble before, all those months ago!! I guess I was just too, too
new at this <g>.

This is super fantastic! Next week I can start locking up every
single workbook I made for the team. Phew, that's going to save a lot
of heartache. They'll be able to sort and everything, but they'll
stop "corrupting" the "interface" <lol>.

Thanks bunches!! :oD

On 30 Dec 2005 12:23:34 -0800, "davegb" <[email protected]> wrote:

>
>StargateFanFromWork wrote:
>> I looked through the archives but have seen that there can be problems with
>> protecting/unprotecting sheets with vb so thought I would just ask about
>> this one. I have buttons on some of my sheets. I'm ready to protect the
>> sheets whereas up till now they remained unprotected. Apparently, we can
>> unprotect sheet, run code, then protect sheet again. (I'm assuming buttons
>> will still be clickable even though protected??)
>>
>> Also, we'll need a generic line of coding for protecting/unprotecting -
>> i.e., without any sheet names and/or passwords - as that would be easiest to
>> deal with as then I could simply copy those lines to all the current
>> workbooks. It would mean adding a simple unprotect line after title of
>> script and have code then execute rest of script - such as sort, etc. - and
>> then protect the sheet before script finishes.
>>
>> Also, people have posted about problems with passwords so we won't need any
>> passwords. And the other problem I saw is that code might not work if sheet
>> protected manually, etc. So it was recommended in that particular post to
>> use an IF statement. I haven't been able to find an example of code that
>> will do these 2 things so begging indulgence of group to help with this one.
>>
>> This is final step in completing all the workbooks I made here at this job 1
>> 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close
>> them up yet still retain full functionality with all the buttons/code! <g>
>>
>> Thank you!! :oD
>
>Have only been working with VBA for less than a year, this time (did a
>little years ago, but not much). But I have been working with password
>protected sheets for almost that long with no problems whatsoever. I've
>just coded the macros to unprotect and the protect, using the
>appropriate password.
>Hope this helps in your world.

Yes, it did work well <fingers crossed>. I didn't use a password but
so far, so good.

On 30 Dec 2005 13:37:46 -0800, "Blue Hornet" <[email protected]>
wrote:

>
>StargateFanFromWork wrote:
>> I looked through the archives but have seen that there can be problems with
>> protecting/unprotecting sheets with vb so thought I would just ask about
>> this one. I have buttons on some of my sheets. I'm ready to protect the
>> sheets whereas up till now they remained unprotected. Apparently, we can
>> unprotect sheet, run code, then protect sheet again. (I'm assuming buttons
>> will still be clickable even though protected??)
>>
>> Also, we'll need a generic line of coding for protecting/unprotecting -
>> i.e., without any sheet names and/or passwords - as that would be easiest to
>> deal with as then I could simply copy those lines to all the current
>> workbooks. It would mean adding a simple unprotect line after title of
>> script and have code then execute rest of script - such as sort, etc. - and
>> then protect the sheet before script finishes.
>>
>> Also, people have posted about problems with passwords so we won't need any
>> passwords. And the other problem I saw is that code might not work if sheet
>> protected manually, etc. So it was recommended in that particular post to
>> use an IF statement. I haven't been able to find an example of code that
>> will do these 2 things so begging indulgence of group to help with this one.
>>
>> This is final step in completing all the workbooks I made here at this job 1
>> 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close
>> them up yet still retain full functionality with all the buttons/code! <g>
>>
>> Thank you!! :oD
>
>
>I've been using this for some time with good success. I have the main
>macro assigned to a custom button on my main toolbar, and it works as a
>toggle to Protect or Unprotect, as I require. The "unprotect" routine
>works regardless of the Protection password used (if any).
>
>Sub ProtectionToggle()
> Application.ScreenUpdating = False
>
> If ActiveSheet.ProtectContents = True Then
>' Unprotect -- a valid command by itself for non-password
>protected sheets
> ThisSheetPassword
> Else
> Protect
> End If
>
> Application.ScreenUpdating = True
>End Sub
>
>******************************************
>
>Sub ThisSheetPassword()
>' Obtained from pcc at MrExcel.com in response to my query
>' about updated PASSWORDS.XLA (to unprotect sheets protected by
>' an unknown password).
>
>On Error Resume Next
>ActiveSheet.Protect "", , , , True
>ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
>
>End Sub
>
>******************************************
>
>And if I have a workbook full of protected sheets, and want to
>unprotect all of them for mass edits or whatever reason, then I use
>this one:
>
>Sub SheetPassword()
>' Obtained from pcc at MrExcel.com in response to my query
>' about updated PASSWORDS.XLA (to unprotect sheets protected by
>' an unknown password).
>
>StartSheet = ActiveSheet.Name
>On Error Resume Next
>For myCounter = 1 To Worksheets.Count
> Worksheets(myCounter).Select
> ActiveSheet.Protect "", , , , True
> ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
>Next myCounter
>
>Sheets(StartSheet).Select
>End Sub

Awesome, thanks! I'll give this a try myself. Looks like really neat
code.

Cheers! :oD

ps. This "feature" to unprotect worksheets was fixed in xl2002. It won't work
there.

StargateFan wrote:
>
> On 30 Dec 2005 13:37:46 -0800, "Blue Hornet" <[email protected]>
> wrote:
>
> >
> >StargateFanFromWork wrote:
> >> I looked through the archives but have seen that there can be problems with
> >> protecting/unprotecting sheets with vb so thought I would just ask about
> >> this one. I have buttons on some of my sheets. I'm ready to protect the
> >> sheets whereas up till now they remained unprotected. Apparently, we can
> >> unprotect sheet, run code, then protect sheet again. (I'm assuming buttons
> >> will still be clickable even though protected??)
> >>
> >> Also, we'll need a generic line of coding for protecting/unprotecting -
> >> i.e., without any sheet names and/or passwords - as that would be easiest to
> >> deal with as then I could simply copy those lines to all the current
> >> workbooks. It would mean adding a simple unprotect line after title of
> >> script and have code then execute rest of script - such as sort, etc. - and
> >> then protect the sheet before script finishes.
> >>
> >> Also, people have posted about problems with passwords so we won't need any
> >> passwords. And the other problem I saw is that code might not work if sheet
> >> protected manually, etc. So it was recommended in that particular post to
> >> use an IF statement. I haven't been able to find an example of code that
> >> will do these 2 things so begging indulgence of group to help with this one.
> >>
> >> This is final step in completing all the workbooks I made here at this job 1
> >> 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close
> >> them up yet still retain full functionality with all the buttons/code! <g>
> >>
> >> Thank you!! :oD
> >
> >
> >I've been using this for some time with good success. I have the main
> >macro assigned to a custom button on my main toolbar, and it works as a
> >toggle to Protect or Unprotect, as I require. The "unprotect" routine
> >works regardless of the Protection password used (if any).
> >
> >Sub ProtectionToggle()
> > Application.ScreenUpdating = False
> >
> > If ActiveSheet.ProtectContents = True Then
> >' Unprotect -- a valid command by itself for non-password
> >protected sheets
> > ThisSheetPassword
> > Else
> > Protect
> > End If
> >
> > Application.ScreenUpdating = True
> >End Sub
> >
> >******************************************
> >
> >Sub ThisSheetPassword()
> >' Obtained from pcc at MrExcel.com in response to my query
> >' about updated PASSWORDS.XLA (to unprotect sheets protected by
> >' an unknown password).
> >
> >On Error Resume Next
> >ActiveSheet.Protect "", , , , True
> >ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
> >
> >End Sub
> >
> >******************************************
> >
> >And if I have a workbook full of protected sheets, and want to
> >unprotect all of them for mass edits or whatever reason, then I use
> >this one:
> >
> >Sub SheetPassword()
> >' Obtained from pcc at MrExcel.com in response to my query
> >' about updated PASSWORDS.XLA (to unprotect sheets protected by
> >' an unknown password).
> >
> >StartSheet = ActiveSheet.Name
> >On Error Resume Next
> >For myCounter = 1 To Worksheets.Count
> > Worksheets(myCounter).Select
> > ActiveSheet.Protect "", , , , True
> > ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
> >Next myCounter
> >
> >Sheets(StartSheet).Select
> >End Sub
>
> Awesome, thanks! I'll give this a try myself. Looks like really neat
> code.
>
> Cheers! :oD

--

Dave Peterson

On Fri, 30 Dec 2005 19:43:04 -0600, Dave Peterson
<[email protected]> wrote:

>ps. This "feature" to unprotect worksheets was fixed in xl2002. It won't work
>there.

Darn. Good to know. I have XL2K so don't know what will happen to
that code when they upgrade ...

What about XL 2003? Are we up to that version? I believe there's an
Outlook 2003 ...

>StargateFan wrote:
>>
>> On 30 Dec 2005 13:37:46 -0800, "Blue Hornet" <[email protected]>
>> wrote:
>>
>> >
>> >StargateFanFromWork wrote:
>> >> I looked through the archives but have seen that there can be problems with
>> >> protecting/unprotecting sheets with vb so thought I would just ask about
>> >> this one. I have buttons on some of my sheets. I'm ready to protect the
>> >> sheets whereas up till now they remained unprotected. Apparently, we can
>> >> unprotect sheet, run code, then protect sheet again. (I'm assuming buttons
>> >> will still be clickable even though protected??)
>> >>
>> >> Also, we'll need a generic line of coding for protecting/unprotecting -
>> >> i.e., without any sheet names and/or passwords - as that would be easiest to
>> >> deal with as then I could simply copy those lines to all the current
>> >> workbooks. It would mean adding a simple unprotect line after title of
>> >> script and have code then execute rest of script - such as sort, etc. - and
>> >> then protect the sheet before script finishes.
>> >>
>> >> Also, people have posted about problems with passwords so we won't need any
>> >> passwords. And the other problem I saw is that code might not work if sheet
>> >> protected manually, etc. So it was recommended in that particular post to
>> >> use an IF statement. I haven't been able to find an example of code that
>> >> will do these 2 things so begging indulgence of group to help with this one.
>> >>
>> >> This is final step in completing all the workbooks I made here at this job 1
>> >> 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close
>> >> them up yet still retain full functionality with all the buttons/code! <g>
>> >>
>> >> Thank you!! :oD
>> >
>> >
>> >I've been using this for some time with good success. I have the main
>> >macro assigned to a custom button on my main toolbar, and it works as a
>> >toggle to Protect or Unprotect, as I require. The "unprotect" routine
>> >works regardless of the Protection password used (if any).
>> >
>> >Sub ProtectionToggle()
>> > Application.ScreenUpdating = False
>> >
>> > If ActiveSheet.ProtectContents = True Then
>> >' Unprotect -- a valid command by itself for non-password
>> >protected sheets
>> > ThisSheetPassword
>> > Else
>> > Protect
>> > End If
>> >
>> > Application.ScreenUpdating = True
>> >End Sub
>> >
>> >******************************************
>> >
>> >Sub ThisSheetPassword()
>> >' Obtained from pcc at MrExcel.com in response to my query
>> >' about updated PASSWORDS.XLA (to unprotect sheets protected by
>> >' an unknown password).
>> >
>> >On Error Resume Next
>> >ActiveSheet.Protect "", , , , True
>> >ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
>> >
>> >End Sub
>> >
>> >******************************************
>> >
>> >And if I have a workbook full of protected sheets, and want to
>> >unprotect all of them for mass edits or whatever reason, then I use
>> >this one:
>> >
>> >Sub SheetPassword()
>> >' Obtained from pcc at MrExcel.com in response to my query
>> >' about updated PASSWORDS.XLA (to unprotect sheets protected by
>> >' an unknown password).
>> >
>> >StartSheet = ActiveSheet.Name
>> >On Error Resume Next
>> >For myCounter = 1 To Worksheets.Count
>> > Worksheets(myCounter).Select
>> > ActiveSheet.Protect "", , , , True
>> > ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
>> >Next myCounter
>> >
>> >Sheets(StartSheet).Select
>> >End Sub
>>
>> Awesome, thanks! I'll give this a try myself. Looks like really neat
>> code.
>>
>> Cheers! :oD

It remains "fixed" in xl2003 <vbg>.

But if there are no passwords, you can just unprotect/protect normally. There
won't be a need to rely on what many would consider a bug.

StargateFan wrote:
>
> On Fri, 30 Dec 2005 19:43:04 -0600, Dave Peterson
> <[email protected]> wrote:
>
> >ps. This "feature" to unprotect worksheets was fixed in xl2002. It won't work
> >there.
>
> Darn. Good to know. I have XL2K so don't know what will happen to
> that code when they upgrade ...
>
> What about XL 2003? Are we up to that version? I believe there's an
> Outlook 2003 ...
>
> >StargateFan wrote:
> >>
> >> On 30 Dec 2005 13:37:46 -0800, "Blue Hornet" <[email protected]>
> >> wrote:
> >>
> >> >
> >> >StargateFanFromWork wrote:
> >> >> I looked through the archives but have seen that there can be problems with
> >> >> protecting/unprotecting sheets with vb so thought I would just ask about
> >> >> this one. I have buttons on some of my sheets. I'm ready to protect the
> >> >> sheets whereas up till now they remained unprotected. Apparently, we can
> >> >> unprotect sheet, run code, then protect sheet again. (I'm assuming buttons
> >> >> will still be clickable even though protected??)
> >> >>
> >> >> Also, we'll need a generic line of coding for protecting/unprotecting -
> >> >> i.e., without any sheet names and/or passwords - as that would be easiest to
> >> >> deal with as then I could simply copy those lines to all the current
> >> >> workbooks. It would mean adding a simple unprotect line after title of
> >> >> script and have code then execute rest of script - such as sort, etc. - and
> >> >> then protect the sheet before script finishes.
> >> >>
> >> >> Also, people have posted about problems with passwords so we won't need any
> >> >> passwords. And the other problem I saw is that code might not work if sheet
> >> >> protected manually, etc. So it was recommended in that particular post to
> >> >> use an IF statement. I haven't been able to find an example of code that
> >> >> will do these 2 things so begging indulgence of group to help with this one.
> >> >>
> >> >> This is final step in completing all the workbooks I made here at this job 1
> >> >> 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close
> >> >> them up yet still retain full functionality with all the buttons/code! <g>
> >> >>
> >> >> Thank you!! :oD
> >> >
> >> >
> >> >I've been using this for some time with good success. I have the main
> >> >macro assigned to a custom button on my main toolbar, and it works as a
> >> >toggle to Protect or Unprotect, as I require. The "unprotect" routine
> >> >works regardless of the Protection password used (if any).
> >> >
> >> >Sub ProtectionToggle()
> >> > Application.ScreenUpdating = False
> >> >
> >> > If ActiveSheet.ProtectContents = True Then
> >> >' Unprotect -- a valid command by itself for non-password
> >> >protected sheets
> >> > ThisSheetPassword
> >> > Else
> >> > Protect
> >> > End If
> >> >
> >> > Application.ScreenUpdating = True
> >> >End Sub
> >> >
> >> >******************************************
> >> >
> >> >Sub ThisSheetPassword()
> >> >' Obtained from pcc at MrExcel.com in response to my query
> >> >' about updated PASSWORDS.XLA (to unprotect sheets protected by
> >> >' an unknown password).
> >> >
> >> >On Error Resume Next
> >> >ActiveSheet.Protect "", , , , True
> >> >ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
> >> >
> >> >End Sub
> >> >
> >> >******************************************
> >> >
> >> >And if I have a workbook full of protected sheets, and want to
> >> >unprotect all of them for mass edits or whatever reason, then I use
> >> >this one:
> >> >
> >> >Sub SheetPassword()
> >> >' Obtained from pcc at MrExcel.com in response to my query
> >> >' about updated PASSWORDS.XLA (to unprotect sheets protected by
> >> >' an unknown password).
> >> >
> >> >StartSheet = ActiveSheet.Name
> >> >On Error Resume Next
> >> >For myCounter = 1 To Worksheets.Count
> >> > Worksheets(myCounter).Select
> >> > ActiveSheet.Protect "", , , , True
> >> > ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
> >> >Next myCounter
> >> >
> >> >Sheets(StartSheet).Select
> >> >End Sub
> >>
> >> Awesome, thanks! I'll give this a try myself. Looks like really neat
> >> code.
> >>
> >> Cheers! :oD

--

Dave Peterson

On Sat, 31 Dec 2005 09:37:25 -0600, Dave Peterson
<[email protected]> wrote:

>It remains "fixed" in xl2003 <vbg>.

<g>

>But if there are no passwords, you can just unprotect/protect normally. There
>won't be a need to rely on what many would consider a bug.

Oh, good. As long as my worksheets work when govt/private sector
upgrades to later versions. A lot of work has gone in to each one
<g>.

Thanks.

>StargateFan wrote:
>>
>> On Fri, 30 Dec 2005 19:43:04 -0600, Dave Peterson
>> <[email protected]> wrote:
>>
>> >ps. This "feature" to unprotect worksheets was fixed in xl2002. It won't work
>> >there.
>>
>> Darn. Good to know. I have XL2K so don't know what will happen to
>> that code when they upgrade ...
>>
>> What about XL 2003? Are we up to that version? I believe there's an
>> Outlook 2003 ...
>>
>> >StargateFan wrote:
>> >>
>> >> On 30 Dec 2005 13:37:46 -0800, "Blue Hornet" <[email protected]>
>> >> wrote:
>> >>
>> >> >
>> >> >StargateFanFromWork wrote:
>> >> >> I looked through the archives but have seen that there can be problems with
>> >> >> protecting/unprotecting sheets with vb so thought I would just ask about
>> >> >> this one. I have buttons on some of my sheets. I'm ready to protect the
>> >> >> sheets whereas up till now they remained unprotected. Apparently, we can
>> >> >> unprotect sheet, run code, then protect sheet again. (I'm assuming buttons
>> >> >> will still be clickable even though protected??)
>> >> >>
>> >> >> Also, we'll need a generic line of coding for protecting/unprotecting -
>> >> >> i.e., without any sheet names and/or passwords - as that would be easiest to
>> >> >> deal with as then I could simply copy those lines to all the current
>> >> >> workbooks. It would mean adding a simple unprotect line after title of
>> >> >> script and have code then execute rest of script - such as sort, etc. - and
>> >> >> then protect the sheet before script finishes.
>> >> >>
>> >> >> Also, people have posted about problems with passwords so we won't need any
>> >> >> passwords. And the other problem I saw is that code might not work if sheet
>> >> >> protected manually, etc. So it was recommended in that particular post to
>> >> >> use an IF statement. I haven't been able to find an example of code that
>> >> >> will do these 2 things so begging indulgence of group to help with this one.
>> >> >>
>> >> >> This is final step in completing all the workbooks I made here at this job 1
>> >> >> 1/2 yrs ago. They've been unprotected all this time. Hopefully I can close
>> >> >> them up yet still retain full functionality with all the buttons/code! <g>
>> >> >>
>> >> >> Thank you!! :oD
>> >> >
>> >> >
>> >> >I've been using this for some time with good success. I have the main
>> >> >macro assigned to a custom button on my main toolbar, and it works as a
>> >> >toggle to Protect or Unprotect, as I require. The "unprotect" routine
>> >> >works regardless of the Protection password used (if any).
>> >> >
>> >> >Sub ProtectionToggle()
>> >> > Application.ScreenUpdating = False
>> >> >
>> >> > If ActiveSheet.ProtectContents = True Then
>> >> >' Unprotect -- a valid command by itself for non-password
>> >> >protected sheets
>> >> > ThisSheetPassword
>> >> > Else
>> >> > Protect
>> >> > End If
>> >> >
>> >> > Application.ScreenUpdating = True
>> >> >End Sub
>> >> >
>> >> >******************************************
>> >> >
>> >> >Sub ThisSheetPassword()
>> >> >' Obtained from pcc at MrExcel.com in response to my query
>> >> >' about updated PASSWORDS.XLA (to unprotect sheets protected by
>> >> >' an unknown password).
>> >> >
>> >> >On Error Resume Next
>> >> >ActiveSheet.Protect "", , , , True
>> >> >ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
>> >> >
>> >> >End Sub
>> >> >
>> >> >******************************************
>> >> >
>> >> >And if I have a workbook full of protected sheets, and want to
>> >> >unprotect all of them for mass edits or whatever reason, then I use
>> >> >this one:
>> >> >
>> >> >Sub SheetPassword()
>> >> >' Obtained from pcc at MrExcel.com in response to my query
>> >> >' about updated PASSWORDS.XLA (to unprotect sheets protected by
>> >> >' an unknown password).
>> >> >
>> >> >StartSheet = ActiveSheet.Name
>> >> >On Error Resume Next
>> >> >For myCounter = 1 To Worksheets.Count
>> >> > Worksheets(myCounter).Select
>> >> > ActiveSheet.Protect "", , , , True
>> >> > ActiveSheet.Range("a1").Copy ActiveSheet.Range("a1")
>> >> >Next myCounter
>> >> >
>> >> >Sheets(StartSheet).Select
>> >> >End Sub
>> >>
>> >> Awesome, thanks! I'll give this a try myself. Looks like really neat
>> >> code.
>> >>
>> >> Cheers! :oD