Code to Hide/Unhide Worksheet

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
 
J

Jacob Skaria

'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
 
B

Barb Reinhardt

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.
 
P

Patrick Molloy

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.
 
B

Barb Reinhardt

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
 
K

keiji kounoike

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
 
Top