Excel's Custom Views

[Jon von der Heyden]

I have a few concurrent projects at the moment. A couple of them are really simple, yet really fun! A major improvement I'm making on both is the set-up of CUSTOM VIEWS.

You can use a custom view to save specific display settings (such as column widths, row heights, hidden rows and columns, cell selections, filter settings, and window settings) and print settings (such as page settings, margins, headers and footers, and sheet settings) for a worksheet so that you can quickly apply these settings to that worksheet when needed. You can also include a specific print area in a custom view.

Job #1: The client uses the template to input actuals, budget and a couple of different forecast versions. The model also houses the prior year figures. Client needs various different summaries. The typical approach would be to create a variety of summaries using tables and various formulae. Instead though I've set-up 13 custom views that hides an unhides columns from one big summary.

Job #2: I'm revamping an existing finance model. The previous developer used a lot of VBA to create different views. By 'views' I don't mean custom views though. The VBA hides and collapses/expands groups of rows and columns. As part of my revamp I've set them all up as custom views and recoded the VBA to simply launch the chosen view. Simples! This way the custom could expand on the list of exiting views themselves.

Two little peeves I have with custom views:
1) They will not work if you use Tables in your workbook (which can be quite a major setback because I LOVE Tables)
2) Worksheet protection can prevent custom views from loading!

So here's how I deal with the protection issue:
Firstly I create a new sheet called 'Views' (codename shtViews). I'll hide this sheet later.
I'll use this sheet to list a range of view names, in column A, and I'll refer to the range use a dynamic range name (d_rng_Views). I like to keep an ordered list hence I tend to prefix view names with a)...b)...c) etc.
I use the workbook open event to create the list of view names in column A:

Private Sub Workbook_Open()
    Dim cvw As Excel.CustomView
    
    With shtViews
        .Range("A2:A" & .Rows.Count).Clear
        For Each cvw In ThisWorkbook.CustomViews
            .Range("A" & .Rows.Count).End(xlUp).Offset(1).Value = cvw.Name
            Call Intersect(.Columns(1), .UsedRange).Sort(Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes)
        Next cvw
    End With
    Call shtInput.Protect(Password:="Unl0ckM3", DrawingObjects:=True, UserInterfaceOnly:=True)
End Sub

Next I create a list validation cell of all of the available views. In the following example I'm using cell B4. Then I use the sheet change event to load the view selected in the list validation cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cvw As Excel.CustomView

    If Intersect(Me.Range("B4"), Target) Is Nothing Then Exit Sub

    On Error Resume Next
        Set cvw = ThisWorkbook.CustomViews(Me.Range("B4").Value)
    On Error GoTo err_Catch

    If cvw Is Nothing Then
        Call Err.Raise(Number:=vbObjectError + 1024, _
                       Description:="The chosen view does not exist!")
    End If
    
    With Me
        Call .Unprotect(Password:="Unl0ckM3")
            cvw.Show
        Call .Protect(Password:="Unl0ckM3", DrawingObjects:=True, UserInterfaceOnly:=True)
    End With

proc_End:
    Exit Sub

err_Catch:
    Call MsgBox(Prompt:=Err.Description, Buttons:=vbOKOnly + vbExclamation, Title:="Error")
    GoTo proc_End
End Sub

I don't have an issue enclosing the sheet password in the code. I tend to use sheet protection to prevent users from stuffing up formulae and structure, not to hide data! In fact I tend to always publish the password in an info sheet anyway!

Toggling through Custom Views via the Ribbon:
I find that when I do use the Ribbon to toggle between views, the current set-up is just not good enough. There is another custom views control available (a combobox that lists each view); only it's not anywhere in the ribbon! I like to add that control to a new group in the Home tab, and again in a new group in the View tab. Here's how:

• Right-click anywhere on the Ribbon
• Click ‘Customize the Ribbon’
• Create a new group within the Home tab (click ‘Home’ tab within Main Tabs and click ‘New Group’). I’ve called my new group ‘Bits & Bobs’.
• Look in All Commands and add ‘Custom Views’ to your new group. Make sure you choose the 2^nd custom views control (i.e. the one without the icon on the left-hand side).
• Click OK

Don't just read this and forget!
There are loads of users that will typically read up about custom views but then completely forget them. After reading this why not read up some more about them. Perhaps set-up some sample files and prepare your Ribbon the way I described above. And perhaps even why not revisit some of your own models and improve them by setting up custom views. Custom views are awesome! Once you familiarise yourself with them you will love them! Using these really can cut back the number of summaries and formulae in your models and simplify your code!

The best Custom Views article on the web (that I know of!)...