OK, good info, Thanks

"Tom Ogilvy" wrote in message
...
Now that J Latham has given you a tutorial on all the foibles of
selecting, I will mention that I have seen problems in terms of very
strange behavior manually selecting on a sheet after that sheet was
protected when it is not the active sheet. Thus I added the code to
replace your Activesheet to include wks.Activate before unprotecting.
This speed penalty is readily and largely mitigated by supressing
screenupdating.

So, perhaps this isn't a prevelant problem, but it is no problem at all
when the sheet is activated before protecting (or unprotecting).

Just some added info.

--
Regards,
Tom Ogilvy



"Dean" wrote in message
...
I meant the macro I posted in my query to the forum.

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
P.S. - you said "...the macro I sent you didn't..." - did you send email
to
me? If so, I haven't received it. My email address is HelpFrom @
jlathamsite.com (remove spaces) for assistance requests related to these
forums. Or maybe you meant "...the macro I showed you didn't..."
referring
to the code in the original post?

"Dean" wrote:

Thanks to all, especially you for explaining all this JL - someday it
will
sink in. My understanding, from some other poster (was it Nick) is
that
it's more efficient not to activate the worksheets and I think your
approach
avoids such, right? In fact, you can probably tell that the macro I
sent
you didn't even work with sheets unhidden. I hybridized my existing
working
macro which did activate sheets; and one from, I think Nick, which
didn't
activate sheets and sent that to you!

The only thing I still find confusing is that I copied the same routine
into
my unprotect all sheets macro, when I unprotect, I see the screen flash
like
crazy whereas, when I protect, it doesn't move at all. I'm pretty sure
the
only difference is one has one row that says unprotect, the other one
protects. No biggie, but I'm curious why. Do you know?

Dean

"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
Corey's solution will work. Perhaps this will also.

First, there's no .Hidden property for the worksheet object (there is
for
rows and columns), so that was a problem.

The second problem was that within your For Each loop your IF test
was
always ONLY looking at the current ActiveSheet - so the result of
that
test
was always the same (and usually the active sheet is visible <g).
When
you
work through a collection as you were doing with the For Each loop,
the
individual members of the collection aren't actually activated, per
se, so
that's why ActiveSheet. was always the same sheet.

Sub TestForHiddenSheets()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
If wks.Visible = xlSheetVisible Then
'...your code to protect/unprotect here
End If
Next
End Sub

So you test the .Visible property. And since there are 3 possible
conditions or values;
xlSheetVisible (just what it says - the sheet is visible in the
workbook)
xlSheetHidden (hidden, but visible in the Format | Sheet [Unhide]
list)
xlSheetVeryHidden (hidden from view completely - not even in the list
of
hidden sheets)
the best thing to test for is the one condition: Is it Visible and if
it
is,
then act on it.

Hope this helps you to some small degree.

"Dean" wrote:

I attempted to edit a macro from you folks to protect all sheets,
because
I
noticed that, if I hid some sheets, then the macro crashed. I added
two
rows, the if statement and the rem statement. This is still
crashing
with a
worksheet hidden. I guess it may be that it can't select a sheet if
it
is
hidden, so that my if statement is too late, or else my syntax is
wrong!
Kindly help.

Thanks!
Dean

Sub ProtectAllWorksheets()
Dim wks As Worksheet
For Each wks In Worksheets
If ActiveSheet.Hidden = True Then GoTo L1

wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True,
_
AllowFormattingRows:=True

L1: Rem
Next wks
End Sub