- Excell 2003, SP2 -

I have a workbook that contains several sheets. Only the first one has
been protected. Through VBA, I'm trying to protect all the sheets, so I
do the following:


Code:
--------------------
Sub LockAll()
Dim wSheet As Worksheet
Worksheets(1).Unprotect Password:="passwd_string"
For Each wSheet In Worksheets
wSheet.Protect Password:="passwd_string"
Next
End Sub
--------------------

Can anyone tell me why it successfully Unprotects and Re-Protects ONLY
the first sheet and doesn't Protect any of the other ones? (Remember,
the other sheets don't start out Protected, only the first one does.)


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=501872

I don't see any reason why the 1st sheet would be protected and other sheets
wouldn't be.

Therefo are you sure this code is even being run? (i.e., what is it that
makes you think that the first sheet is being unprotected & reprotected?)

My suspicion is that SubLockAll isn't being called and that the 1st sheet
remains untouched rather than "restored" to its original state.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"AMK4" wrote in message
...

- Excell 2003, SP2 -

I have a workbook that contains several sheets. Only the first one has
been protected. Through VBA, I'm trying to protect all the sheets, so I
do the following:


Code:
--------------------
Sub LockAll()
Dim wSheet As Worksheet
Worksheets(1).Unprotect Password:="passwd_string"
For Each wSheet In Worksheets
wSheet.Protect Password:="passwd_string"
Next
End Sub
--------------------

Can anyone tell me why it successfully Unprotects and Re-Protects ONLY
the first sheet and doesn't Protect any of the other ones? (Remember,
the other sheets don't start out Protected, only the first one does.)


--
AMK4
------------------------------------------------------------------------
AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=501872

You should think about explicitly specifying which workbook you are
operating on.
If the target workbook isn't the active one then your results may not match
your expectations.

Tim

--
Tim Williams
Palo Alto, CA


"AMK4" wrote in message
...

- Excell 2003, SP2 -

I have a workbook that contains several sheets. Only the first one has
been protected. Through VBA, I'm trying to protect all the sheets, so I
do the following:


Code:
--------------------
Sub LockAll()
Dim wSheet As Worksheet
Worksheets(1).Unprotect Password:="passwd_string"
For Each wSheet In Worksheets
wSheet.Protect Password:="passwd_string"
Next
End Sub
--------------------

Can anyone tell me why it successfully Unprotects and Re-Protects ONLY
the first sheet and doesn't Protect any of the other ones? (Remember,
the other sheets don't start out Protected, only the first one does.)


--
AMK4
------------------------------------------------------------------------
AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=501872

Thanks for the reply. Yes it is being run. I've stopped the Sub afte
the first Unprotect line and checked the sheets. That's how I foun
out that the first sheet is being Unprotected successfully. Then
went a step further and see if it's actually running through all th
sheets (by sticking a MsgBox in the For loop) and that's also true.

So I now know that a) it Unprotects the first one as it should, b) i
goes through all the sheets as it should, and c) it only Re-Protect
the first sheet and doesn't on any of the other ones, eventhough
according to the (temporary) MsgBox I placed, it is looping through al
the sheets.

This has me ripping my hair out.


George Nicholson Wrote:
I don't see any reason why the 1st sheet would be protected and othe
sheets
wouldn't be.

Therefo are you sure this code is even being run? (i.e., what is i
that
makes you think that the first sheet is being unprotected
reprotected?)

My suspicion is that SubLockAll isn't being called and that the 1s
sheet
remains untouched rather than "restored" to its original state

--
AMK
-----------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...fo&userid=1914
View this thread: http://www.excelforum.com/showthread.php?threadid=50187

Normally I avoid selection, but for this type of situation, I have found it
more successful to select

Sub LockAll()
Dim wSheet As Worksheet
Worksheets(1).Select
Worksheets(1).Unprotect Password:="passwd_string"
For Each wSheet In Worksheets
wSheet.Select
wSheet.Protect Password:="passwd_string"
Next
End Sub

--
Regards,
Tom Ogilvy


"AMK4" wrote in message
...

- Excell 2003, SP2 -

I have a workbook that contains several sheets. Only the first one has
been protected. Through VBA, I'm trying to protect all the sheets, so I
do the following:


Code:
--------------------
Sub LockAll()
Dim wSheet As Worksheet
Worksheets(1).Unprotect Password:="passwd_string"
For Each wSheet In Worksheets
wSheet.Protect Password:="passwd_string"
Next
End Sub
--------------------

Can anyone tell me why it successfully Unprotects and Re-Protects ONLY
the first sheet and doesn't Protect any of the other ones? (Remember,
the other sheets don't start out Protected, only the first one does.)


--
AMK4
------------------------------------------------------------------------
AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=501872

Good point, however... When there's only one workbook open, and the
macro being run was called from said workbook, this shouldn't matter.
Unless I'm mistaken.

Tim Williams Wrote:
You should think about explicitly specifying which workbook you are
operating on.
If the target workbook isn't the active one then your results may not
match
your expectations.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=501872

No dice. In fact, .Select fails if the sheet's hidden, at least it did
for me. If the sheets are unhidden, then .Select works. When I
changed it to an .Activate call, it worked.

However, it's still not Protecting any of the other sheets, only the
first one gets affected.

I've now tried it with both all the sheets visible (Unhidden) as well
as with them hidden. I've tried your suggestion with both .Select as
well as .Activate. I've tried starting off with the first sheet
Unprotected, see if that makes a difference. Nothing seems to make it
want to Protect any of the other sheets except the first one.

Argh.


Tom Ogilvy Wrote:
Normally I avoid selection, but for this type of situation, I have found
it
more successful to select

Sub LockAll()
Dim wSheet As Worksheet
Worksheets(1).Select
Worksheets(1).Unprotect Password:="passwd_string"
For Each wSheet In Worksheets
wSheet.Select
wSheet.Protect Password:="passwd_string"
Next
End Sub

--
Regards,
Tom Ogilvy


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=501872

You aren't mistaken. However from a "defensive" programming perspective
it's worth the small extra effort.

Otherwise you (me) find yourself with a whole bunch of bits of code which
only work in one specific scenario and have to be modified if you have
*two* workbooks open.

Tim

--
Tim Williams
Palo Alto, CA


"AMK4" wrote in message
...

Good point, however... When there's only one workbook open, and the
macro being run was called from said workbook, this shouldn't matter.
Unless I'm mistaken.

Tim Williams Wrote:
You should think about explicitly specifying which workbook you are
operating on.
If the target workbook isn't the active one then your results may not
match
your expectations.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=501872

Okay, so now I feel like an idiot. It does Protect the sheets! But,
with a twist.

Remember I said Sheet1 starts off Protected while the others didn't?
Well, when I manually Protect a sheet, I uncheck everything except the
'select unlocked cells' option. So, when I was checking the other
sheets after the Sub ran, it allowed me to select any of the locked
cells as well, and this threw me off. See, I never tried to actually
CHANGE any of the values in the (locked) cells. I was just selecting
the cells and thought the sheet wasn't locked in the first place. What
I was forgetting was to set the .EnableSelection method to
xlUnlockedCells prior to Protecting the sheet.

However, this does bring up a question: why would it Protect Sheet1
based on how it was previously Protected, while it only locks the
others with the default settings? Is that just a feature of the
program, by relocking with whatever options were enabled at first?

Thanks to everyone who tried to help and take the time to try to solve
the problem. At least now it's behaving as expected.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=501872