- 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:
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.)
Please Login or Register to view this content.
Last edited by AMK4; 01-16-2006 at 07:49 PM.
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,
--
George Nicholson
Remove 'Junk' from return address.
"AMK4" <[email protected]> wrote in message
news:[email protected]...
>
> - 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" <[email protected]> wrote in message
news:[email protected]...
>
> - 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 after the first Unprotect line and checked the sheets. That's how I found out that the first sheet is being Unprotected successfully. Then I went a step further and see if it's actually running through all the 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) it goes through all the sheets as it should, and c) it only Re-Protects the first sheet and doesn't on any of the other ones, eventhough, according to the (temporary) MsgBox I placed, it is looping through all the sheets.
This has me ripping my hair out.
Originally Posted by George Nicholson
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.
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" <[email protected]> wrote in message
news:[email protected]...
>
> - 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
>
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.
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
Oh, and Tim? Could you tell me how I can specify what workbook to use, if there are multiple books open? Like I mentioned before, the macros are called from this one workbook, by the user clicking on a button on Sheet1, though I suppose one could run any of the macros by going through the menu option Tools -> Macro... which won't work of course.
Last edited by AMK4; 01-17-2006 at 12:33 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks