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:

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):

Tagged with: , ,