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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
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):
|
1 2 3 4 5 |
Sub Manipulator() If ActiveSheet.Shapes("Display Headings").ControlFormat.Value = 1 Then ActiveSheet.Shapes("Display Headings").ControlFormat.Value = 0 End If End Sub |