Consulting

Results 1 to 3 of 3

Thread: worksheet hiding depending on content of worksheet cell

  1. #1
    VBAX Newbie
    Joined
    Dec 2013
    Posts
    2
    Location

    worksheet hiding depending on content of worksheet cell

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

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,279
    Location
    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

  3. #3
    VBAX Newbie
    Joined
    Dec 2013
    Posts
    2
    Location
    Thanks for the help! It works and I could also adapt it to hide categories of sheets (based on A1 content).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •