Toggle

[Rob]

Excel 2000

I require a single macro to toggle between hiding and showing several
worksheets based on their current status i.e. if a sheet is hidden, show it
and if a sheet is shown, hide it. That said, if the sheet is named Menu,
ignore it.

I can do this with separate macros but trying to loop through all sheets and
apply either hide or show depending on the sheets visible property doesn't
work.

Any pointers greatly welcome.

Thanks, Rob

 

[JE McGimpsey]

one way:

Public Sub ToggleHiddenSHeets()
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWorkbook.Worksheets
With wsSheet
If .Name <> "Menu" Then _
.Visible = Not (.Visible = True)
End With
Next wsSheet
End Sub

 

[Bob Phillips]

As Visible evaluates to True or False this line

.Visible = Not (.Visible = True)

can be reduced to

.Visible = Not .Visible


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

Note that doing that throws a run-time error if .Visible = xlVeryHidden

 

[Rob]

JE & Bob,
Thanks for the reply which has done the job. Not quite sure what and how
WITH is working but will read up via help files. Is it correct that the
code should go in the Modules folder?

Regards, Rob

 

[Bob Phillips]

It could be argued that very hidden sheets should not be made visible
anyway?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Yes, in a standard code module.

With is used to set up an object variable, and all dot (.) properties and
methods used after that are properties or methods associated with that
object variable.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

Sure, but in that case you still have to trap the error...

With wsSheet
If Not .Visible = xlVeryHidden Then
.Visible = Not .Visible
End With

 

[Rob]

Thanks for the replies, I've got the toggle working as required. I shall
read up on WITH as this seems to greatly reduce the code required.

Thanks again. Rob

 

[Rob]

Thanks Bob and JE. I'll read up on WITH as this looks very useful, also, the
(.visible = True) in brackets is the first time i've seen this used as an
OR.

Thanks, Rob

 

[JE McGimpsey]

It's really not being used as an OR, it's just there to make the order
of operations obvious (i.e, that ".Visible = TRUE" is evaluated first,
then passed to Not, rather than evaluating "Not .Visible" and then
comparing the result to True).