Solved: Hiding sheets

[MacDaddy]

Good morning all,

I'm trying to write a macro to hide sheets in a workbook based on a value in a cell in the sheet.

It works fine unless a sheet in the workbook is already hidden.

As you'll probably see I'm not that great at VBA programming so can somebody point me in the right direction of how to hide sheets and skip sheets that are already hidden?

Here's the code I've written:

Sub HideSheets()
On Error Resume Next
Sheets("Home").Select
Dim wSheet As Worksheet
For Each wSheet In Worksheets
If Range("AZ65536").Value = "HiddenTrue" Then
Sheets(ActiveSheet.Name).Visible = False
Else: Sheets(ActiveSheet.Index + 1).Select
End If
Next wSheet
End Sub

Like I say, it works if ALL sheets are visible, I need it to work regardless of whether some sheets are already hidden or not, but only where the value in cell AZ65536 states that the sheet is to be hidden.

Many thanks in advance,

Paul

Thanks

[MacDaddy]

I've sort of overcome the problem by adding:



For Each c In Sheets
c.Visible = True
Next c


before the previously posted code, but this seems rather messy.

If there's a better way of achieving the end result I'd love to hear it.

Thanks again,

Paul

[GTO]

Greetings MacDaddy,

You must keep at least one sheet visible at all times (barring running as an AddIn). Is there one sheet that should always stay visible?

Mark

[Bob Phillips]

Greetings MacDaddy,

You must keep at least one sheet visible at all times (barring running as an AddIn).

That still requires one worksheet in the addin workbook to have the visible property set to visible, Excel just hides the workbook.

[GTO]

That still requires one worksheet in the addin workbook to have the visible property set to visible, Excel just hides the workbook.

Well dang, I'm not even sure how I'd spell the sound that just escaped my mouth! I have never thought IsAddin through (yes I know, rather obvious). Thank-you :-)

[MacDaddy]

Greetings MacDaddy,

You must keep at least one sheet visible at all times (barring running as an AddIn). Is there one sheet that should always stay visible?

Mark

Yes, the Home sheet is always visible, thanks.

Paul

[GTO]

Hi Paul,

Hope you caught Bob's (XLD) correction to what I said before; and sorry for the delayed response... evidently had run myself outta sleep...

In reference to your code, hopefully I'll be able to explain this sensibly, as there is a bit of a glitch with your current approach.

To see this, first lets set up a new blank workbook with 6 sheets. Make sure the added sheets are in order, so that from left to right, the tabs are Sheet1, Sheet2, Sheet3, etc.

Now, rename Sheet2 to 'Home'; then, place "HiddenTrue" in AZ65536 of Sheet1, Sheet4, and Sheet6.

In a Standard Module, place your code as modified below:

Sub HideSheetsxx()
'On Error Resume Next
Sheets("Home").Select
Dim wSheet As Worksheet
For Each wSheet In Worksheets
'// See which sheet is referenced by the current wSheet, vs. which sheet is
'// active and thus being referenced by the unqualified Range("AZ65536")
Debug.Print wSheet.Name & vbTab & Range("AZ65536").Parent.Name

If Range("AZ65536").Value = "HiddenTrue" Then
Sheets(ActiveSheet.Name).Visible = False
Else: Sheets(ActiveSheet.Index + 1).Select
End If
Next wSheet
End Sub

Now resize the overall code window so that you can see the sheet tabs.

Then (still in VBIDE/the code window), go to the menubar and select View|Immediate Window.

Finally, we are ready to test. Place the cursor in the procedure and start pressing the F8 key. As you step thru the code, executing line-by-line, your will see this:

First, our 'Home' sheet is selected, then our For Each...Next loop is entered. In the first loop, you will see that Sheet1 is the first sheet to be referred to by wSheet, but that the Range being referred to belongs to Home!

As you continue looping, you will see that the Range always belongs to the Activesheet instead of the current wSheet. You will also see that the code fouls in the last loop (as when it gets to the last sheet, it still has one more loop to make and Activesheet.Index + 1 refers to a non-existent sheet) and that Sheet1 was never hidden.

So, while we won't try and skip past hidden sheets, we can test all the sheets properly by ensuring that we are checking the range's value (the cell value) of the current wSheet like:

Option Explicit

Option Explicit

Sub exa()
Dim wSheet As Worksheet

Worksheets("Home").Select
For Each wSheet In ThisWorkbook.Worksheets
'// Test against AZ65536 of the current wSheet and
'// make sure not to hide the 'Home' sheet
If wSheet.Range("AZ65536").Value = "HiddenTrue" _
And Not wSheet.Name = "Home" Then
wSheet.Visible = xlSheetVeryHidden 'or xlSheetHidden
End If
Next
End Sub


In the above, we qualify the Range with wSheet. This tells Excel what sheet we want the Range from. Elsewise, Excel presumes we are wanting the range from whatever sheet happens to be active.

Does that help?

Mark

[MacDaddy]

Hi Mark,

First of all apologies for my late reply. I didn't get your response until I left the office on Friday and so couldn't access my project and include your code.

I was aware my code was problematic - after I posted my initial plea for help I did amend it slightly to stop it falling over when it got to the last sheet, although that was very Heath Robinson too :hammer:, but I was still nervous that as you rightly pointed out, if the 'Home' sheet wasn't the very first sheet in the workbook anything prior to it would be ignored.

So thanks not just for helping me with the correct code but also for taking the time to explain where I was going wrong with my approach to the problem, very much appreciated, Mark.

Best regards,

Paul

[GTO]

You bet Paul, :beerchug: Very happy to help :)

Mark