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
ActiveWindow.SplitColumn = 0
If Len(Cells(19, 1).Text) > 0 Then
ActiveWindow.Caption = Cells(19, 1).Text
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
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):
If ActiveSheet.Shapes("Display Headings").ControlFormat.Value = 1 Then
ActiveSheet.Shapes("Display Headings").ControlFormat.Value = 0