Code to Hide/Unhide Worksheet

  • Thread starter Thread starter Barb Reinhardt
  • Start date Start date
B

Barb Reinhardt

I want to do something like this

Sub HideUnhideSheets(myWS As Excel.Worksheet, myHidden As Variant)
If myWS.Visible <> myHidden Then
myWS.Visible = myHidden
End If

End Sub

What I want to do is set it up so that myHidden only allows xlVisible,
xlHidden and xlVeryHidden (or whatever they are). How do I do that?

Thanks,
Barb Reinhardt
 
'visible
myWS.Visible = 1
'hidden
myWS.Visible = 0
'very hidden
myWS.Visible = 2

To toggle between hide and unhide; try

myWS.Visible = not myWS.Visible

If this post helps click Yes
 
Thanks, but I already have this information. I've seen a way somewhere on
how to have a variable default to some specified options for values when the
sub is called, such that only allows Hidden, Visible and VeryHidden. That's
what I'm looking for.
 
a Hidden sheet can still be seen, and possibly unhidden by the user through
the Format/Sheets/Unhide menu - hidden sheets are listed here. However
VeryHidden sheets cannot be seen in this list. they can only be seen in the
workbooks property window and listed in code.
 
I understand all that. I'm still looking for code for something like this

Sub Test (myVal as variant)

where I can programmatically define several discrete options for myVal.
Only those values are allowed. I've seen it on other code, but just can't
find it. It's something that done outside of the procedure as I recall.

Barb Reinhardt
 
This is not what you saw someone's code. but what about checking you
arguments before starting procedure like below?

Sub testcall()
test 123
End Sub

Sub test(myval As Variant)
Select Case myval
Case xlSheetHidden, xlSheetVisible, xlSheetVeryHidden
Case Else
'Msgbox is not needed, just for test
MsgBox "Wrong arg:" & myval
Exit Sub
End Select

'start your code from here
MsgBox myval

End Sub

Keiji
 
Back
Top