VBA – Customize Active Window in Excel

In this article I present a good possibility to customize your Active Window from Excel itself and not from the ribbon. By inserting some form controls, we receive a decent-looking customization dashboard like the one below:

The idea of the so-called dashboard is to perform the following options upon a check:

Show / Hide Formula Bar
Show / Hide Gridlines
Show / Hide Horizontal and Vertical scrollbars
Show / Hide Workbook tabs
Show / Hide Page Breaks
Show / Hide Command Bars

If the following things do not speak clearly to you, you should download the tool and try it. It is really fascinating to gain such control over your spreadsheet.
Right to left is an option to mirror your Excel spreadsheet. Splitting would split the first four columns.
At the bottom of the screenshot you see two more options of this file = a possibility to name the file with any allowed name and a possibility to change the zoom.
Here is the code, which is set in the worksheet itself:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Application.DisplayFormulaBar = Cells(2, 5).Value
    ActiveWindow.DisplayGridlines = Cells(4, 5).Value
    ActiveWindow.DisplayHeadings = Cells(6, 5).Value
    ActiveWindow.DisplayHorizontalScrollBar = Cells(8, 5).Value
    ActiveWindow.DisplayVerticalScrollBar = Cells(10, 5).Value
    ActiveSheet.DisplayPageBreaks = Cells(12, 5).Value
    ActiveWindow.DisplayWorkbookTabs = Cells(14, 5).Value
    Application.CommandBars("Status Bar").Visible = Cells(16, 5).Value
    ActiveWindow.DisplayRightToLeft = Cells(18, 5).Value
    ActiveWindow.GridlineColorIndex = xlColorIndexAutomatic

    If Cells(20, 5).Value = True Then
        ActiveWindow.SplitColumn = 4
    Else
        ActiveWindow.SplitColumn = 0
    End If

    If Len(Cells(19, 1).Text) > 0 Then
        ActiveWindow.Caption = Cells(19, 1).Text
    End If

    If IsNumeric(Cells(21, 1)) Then
        If (Cells(21, 1).Value > 50 And Cells(21, 1).Value < 200) Then
            ActiveWindow.Zoom = Cells(21, 1).Value
        End If
    End If

End Sub

By looking at the code, you probably realize how the customization dashboard is made – there is no reference to a checkbox as far as they are not comfortably accessible via VBA*. Thus, I have hidden column “E”, where a reference is kept for the checkbox selections. So, last but not least = how does it work? If you take a look at the first line of the code, you will realize that it is activated on a right click of the mouse.

At last – here is the Excel file with the code. Enjoy the code! 🙂

*if you want to manipulate a form control checkbox (which is my case) you should use the following code (switching the value of “Display Heading from True to False):

Sub Manipulator()
If ActiveSheet.Shapes("Display Headings").ControlFormat.Value = 1 Then
    ActiveSheet.Shapes("Display Headings").ControlFormat.Value = 0
End If
End Sub

VBA – Customize Active Window in Excel

Related posts: