Hide Multiple Worksheets

Debra had a great post about hiding worksheets with a macro the other day. I wanted to demonstrate another way using Custom Views. Start with a workbook with four sheets: Inputs, Outputs, Tables, and Settings.

When you’re actively developing the workbook, you want to see all of the sheets. But when the workbook is in use by its regular users (in production, we’ll call it) you only want to show the Inputs and Outputs sheets. With all of the worksheets visible, select Custom View from the View menu (View tab – Custom Views in 2007).

Click the Add button to create a new view and call it Development

Close out the dialog and hide the “admin” sheets. Now repeat the steps to create a Production view

To quickly change the visibility of the sheets, simply change the view from Development to Production and back

If you want to make it quicker with a macro, this will toggle between the two:

Sub ChangeViews()
   
    If wshTables.Visible Then
        ThisWorkbook.CustomViews(“Production”).Show
    Else
        ThisWorkbook.CustomViews(“Development”).Show
    End If
   
End Sub

I don’t know how to explicitly check if a View is active, so I check to see if the Tables sheet is hidden. If you know a better way, please let me know in the comments.

Posted in Uncategorized

12 thoughts on “Hide Multiple Worksheets

  1. I like to make helper macros as generic as possible, so I’d probably do something like giving all the hidden sheets the same tab colour, and then hide/show all sheets with this colour. Then you can add more sheets without messing with code.

  2. An interesting alternative.

    I went with an old style approach for our data input models, and used a macro to cycle through a list of worksheets – depending upon what was needed at the time.

    We have a list in column x, followed by 3 further adjacent columns which are populated by a Y or an N to denote whether the worksheet should be visible according to the criteria that is set for these columns.

    The macro is called automatically by whatever process might need to change which sheets are displayed, so it is invisible to the users. In addition there are 2 macros which we use to help us out – showall, and hidenotdisplayed.

    A simple approach but very effective.

  3. Tab colour is good, but I avoid them if possible. Excel 2000 doesn’t have tab colours and whilst it is a 10 year old product I’m still having to develop for it.

  4. A timely hint, right on the button, Thank You.

    I use a generic function (in my library, natch!) to determine whether I am in production or development mode:

    Function blnIAmAtHome()
        blnIAmAtHome = blnFolderExists(“T:GreavesClientsLLSUCLSUTProjectsAttendanceSystem”)
        ‘Sub TESTblnIAmAtHome()
       ‘    Debug.Assert blnIAmAtHome
       ‘End Sub
    End Function

    The path/file represents a file that will only be found on my system, never on the client’s system.
    Your toggling macro might then be augmented by a Workbook.Open event that asks:

    If blnIAmAtHome Then
       (“Development” view)
    Else
       (“Production” view)
    EndIf
  5. There has to be a better way:

    Public Function WhatView()
        Dim cbxView As CommandBarComboBox
       
        If Application.CommandBars.FindControl(ID:=950).Parent.Name = “View” Then
            With Application.CommandBars.Add(Temporary:=True)
                .Controls.Add ID:=950
                .Enabled = True
                .Visible = True
            End With
        End If
       
        Set cbxView = Application.CommandBars.FindControl(ID:=950)
       
        WhatView = cbxView.Text
    End Function
  6. I test for the presence of an empty text file in the workbook’s directory to make the hidden stuff appear. I indicate the sheets which should be hidden either with a hidden worksheet level name or a prefix to the sheet name, or for large projects, I use a master sheet that lists information about each sheet (hidden/visible, address of visible range, other information).

  7. Always thought Custom views could only be used to store hidden rows/columns setting or Filter settings. Never realized it could be used for hiding/un-hiding sheets

  8. Oh…forgot to mention.
    You need to be a bit careful of using custom views in 2007/2010 – If there is a sheet that has Tables – then custom views are disabled for the entire file…Cant see the logic…but thats how it is.

  9. Hi,

    Hadn’t thought of using this for ‘production’ and ‘development’ versions, great idea though =] Think I’ll start using this going forward…

    As for making the macro more generic I tend to use the index as opposed to the name. So if we assume that the Development View will always come first we can use:

    thisworkbook.CustomViews(1).Show

    So something like this in the workbook open event:

    if environ(“UserName”) = “Arthur.Nicholson” then ‘Only person who needs to see admin sheets
    thisworkbook.CustomViews(1).Show
    else
    thisworkbook.CustomViews(1).Show
    end if


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *