Problems with Protect Method

[AMK4]

- 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

 

[George Nicholson]

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

Therefore: 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,

 

[Tim Williams]

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

 

[AMK4]

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.

 

[Tom Ogilvy]

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

 

[AMK4]

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.

 

[AMK4]

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.

 

[Tim Williams]

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

 

[AMK4]

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.