Hide and UNhide Sheet (True/False)

[HotNumbers]

How do i check to see if a sheet is hidden if so Unhide it if not Hide it? Hide or Unhide

 

[Hermanito]

Microsoft, in its endless wisdom, has made it so that a worksheet is not simply hidden or not hidden, true or false. No, the Visible property of a worksheet can have 3 values:
-1, or xlSheetVisible
0, or xlSheetHidden
2, or xlSheetVeryHidden

the only difference between hidden and very hidden is that you cannot unhide a very hidden sheet from within Excel, only with VBA or within the VBE window, in the worksheet properties.

How do you want to detect it? In VBA?

 

[HotNumbers]

I currently have these two codes: I want the ability to click on a button where if the sheet is hidden to Unhideit and if is not hidden the HIDE it.

for hiding

Sub HideAdPubRecharge()

Sheets("Basics BW").Visible = xlVeryHidden
Sheets("Prints BW").Visible = xlVeryHidden
Sheets("Print Rebates BW").Visible = xlVeryHidden
Beep
End Sub

To unhide I have these

Sub UnHideALL()

Sheets("Basics BW").Visible = True
Sheets("Prints BW").Visible = True
Sheets("Print Rebates BW").Visible = True
Beep

End Sub

 

[Hermanito]

Something like this maybe:

Public Sub SwapVisibility(SomeSheet As Worksheet)
   If SomeSheet.Visible = xlSheetVisible Then
      SomeSheet.Visible = xlSheetVeryHidden
   Else
      SomeSheet.Visible = xlSheetVisible
   Endif
End Sub

and call that code like this

SwapVisibility Sheets("Basics BW")

which you can put in your button click eventhandler

 

[HotNumbers]

So do i put the private sub in the Sheet or sheets i want to hide and the other into a moduel

 

[Rick Rothstein]

You do not have to test the sheet in order to change its setting... you can just use one line of code to toggle the setting everytime its executed. I don't know if you want them all toggled at once or individually though. Here is the general idea though (for the xlSheetVisible/xlVeryHidden toggle)...

Sheets("Basics BW").Visible = 1 - Sheets("Basics BW").Visible

Just do the same thing for each sheet you want to set up a toggle for. By the way, the number 1 comes from adding the two possible constant values together (+2 and -1). In general...

Sheets("Basics BW").Visible = (xlVeryHidden + xlSheetVisible) - Sheets("Basics BW").Visible

If you look at the structure of this, it should be obvious why the values toggle.

 

[HotNumbers]

THANKS A million... to both of you ... this is what i needed. RICK your one line code is exactly what i needed.

 

[Hermanito]

Rick: when your code encounters a sheet that is just Hidden, not VeryHidden, unexpected things will happen...

 

[Rick Rothstein]

Rick: when your code encounters a sheet that is just Hidden, not VeryHidden, unexpected things will happen...

True, which is why I qualified my code by saying...

"Here is the general idea though (for the xlSheetVisible/xlVeryHidden toggle)"

The OP specifically showed toggling between xlSheetVisible/xlVeryHidden and since the setting is sheet specific, I presumed he would never run into the xlSheetHidden setting without first knowing it could be set to that.

 

[HotNumbers]

Rick: Your code works I tested Hermintos concerns as well and the code does compensate for the issue. I think this is the simplest one line code I have come across.

I came across code that was over 20 rows... this is so much easier. Somple code like this that does a big job should be posted for others to quickly use. I read a lot of MRExcel and Ozgrid postings none were as simple as this.

Again to both of you thanks a million... and also for Hermintos concern that the code may fail and Rick for your compensation in the code for such issues should they arise.