How to create custom view options for specific sheets using VBA?

To create custom view options for specific sheets using VBA in Excel, you can leverage VBA to programmatically adjust settings and apply preferences for those sheets. Custom views can include settings like zoom level, hidden columns or rows, filtered data, and more. However, it is important to note that while Excel has a built-in “Custom Views” feature, this feature does not work directly with filtered lists or Tables. Instead, you can create a customized setup using VBA.

Here’s an example of how to create a VBA macro to apply custom view options to a specific sheet:

Sub ApplyCustomViewOptions()
    Dim ws As Worksheet

    ' Set the worksheet to apply custom view options
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Make the sheet active
    ws.Activate

    ' Set zoom level
    ws.Application.ActiveWindow.Zoom = 90

    ' Hide specific rows or columns
    ws.Rows("5:10").Hidden = True
    ws.Columns("C:C").Hidden = True

    ' Apply a filter if needed (this does not save to custom view)
    ws.Range("A1:D1").AutoFilter Field:=1, Criteria1:="Example"

    ' Freeze panes example
    ws.Range("B2").Select
    ws.Application.ActiveWindow.FreezePanes = True

    ' Set print area
    ws.PageSetup.PrintArea = "$A$1:$D$20"

    ' Set page orientation
    ws.PageSetup.Orientation = xlLandscape

    ' Activate gridlines and headings view options
    ws.Application.ActiveWindow.DisplayGridlines = True
    ws.Application.ActiveWindow.DisplayHeadings = False

    ' Additional customizations can be added as needed here
End Sub

Steps to Implement:

Open the Visual Basic for Applications (VBA) Editor:

Press `ALT + F11` in Excel to open the VBA editor.

Insert a Module:

In the VBA editor, go to `Insert` > `Module` to create a new module.

Copy the VBA Code:

Copy the provided VBA code into the new module.

Customize the Code:

Replace `”Sheet1″` with the name of your specific sheet.
Adjust the parameters for zoom, hidden rows/columns, filtering, freezing panes, etc., as per your needs.

Run the Macro:

You can run the macro by pressing `F5` while in the VBA editor, or by returning to Excel, pressing `ALT + F8`, selecting `ApplyCustomViewOptions`, and clicking `Run`.

Important Considerations:

Filtering: Keep in mind that certain features like filtering do not save to the Excel Custom Views if your data is formatted as a Table.
Protection: If your sheet is protected, ensure that any rows or columns you attempt to hide or unhide are not blocked by protection settings.
Testing: Test the macro on a copy of your worksheet to ensure it behaves as expected.

VBA provides powerful ways to customize view options beyond what is easily achievable with built-in Excel custom views, especially for sheets where the “Custom Views” feature is limited or not applicable.

facebooklinkedin