PDA

View Full Version : [SOLVED] worksheet hiding depending on content of worksheet cell



svokke
12-02-2013, 01:31 PM
I have an excel 2010 workbook with 50+ worksheets. For the users of this worksheet it is easier if most sheets are hidden. Therefore I would like to hide all sheets, except the worksheets which have "Macropage" as content in cell A1.
I have written following code:


Sub HidePart()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If Range("A1").Value <> "Macropage" Then
ActiveWindow.SelectedSheets.Visible = False
End If
Next ws
End Sub


This code works most of the time.
It doesn't work if the Macro is started from a worksheet which has "Macropage" as content in cell A1. In that case not a singe worksheet is hidden and the macro exits without error message. :banghead:
If it is started from a worksheet with an emty cell A1, it works flawlessly.

Can anyone explain why this macro won't work when it is started from a worksheet with "Macropage" in cell A1?

Paul_Hossler
12-03-2013, 08:15 AM
After a quick look, this always works on the Activesheet



If Range("A1").Value <> "Macropage" Then



Not tested but try this. It tests A1 on the ws



If ws.Range("A1").Value <> "Macropage" Then



Further suggestion / comment. Assumes that there is at least one sheet that will be visible




Sub HidePart()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.Range("A1").Value <> "Macropage" Then ws.Visible = xlSheetHidden
Next ws
End Sub





Paul

svokke
12-04-2013, 11:45 AM
Thanks for the help! It works and I could also adapt it to hide categories of sheets (based on A1 content).