It is no secret to anyone that Microsoft Excel is one of the best data analysis programs that can exist. Along with Microsoft Word, they are the programs with the most installations or with the most users around the world.
How to Use the ActiveX Toggle Button to Show and Hide Columns or Rows
There are courses of different levels to learn to use Microsoft Excel. Several of its tools can become complicated due to the use of the programming language.
In this opportunity, you will learn about the ActiveX toggle button and how to apply it to show or hide rows and columns .
The toggle button is a combination of a command button and a check box . They are usually used to indicate states (yes or no, on or off), that is, toggle between enabled or disabled states when performing an action.
In addition to show or hide columns, it can also be used to display graphics and other elements that are in your Excel workbook.
To start using this Microsoft Excel tool , the first thing you have to do is go to the “Developer” tab found in the program’s ribbon. If this tab is not enabled, you can do the following:
- Right click on the ribbon.
- Select the option to customize ribbon.
- Check the “Developer” box.
Once this tab is enabled, head over to it so you can use the tools you’ll need to insert the toggle button:
- Click on the developer tab .
- Locate and select the insert option.
- A menu will be displayed in which you will locate, at the bottom, the ActiveX controls section.
- Among the last buttons is the toggle button option.
- Select it and start drawing it on the sheet.
Having drawn the toggle button in your Excel workbook, it’s time to review and configure its properties . Once the design mode that appears when selecting the developer tab is activated, you can click on the properties button.
- A window will open showing you all the values of the toggle button.
- In this box you can change the name of the button, colors, size, among other things.
- In the ” LinkedCell ” box, you must specify the cell with which you will link your toggle button.
- When you have already assigned the corresponding name to the action that the button or buttons are going to perform, you can click on the “View code” button that is just below the “Properties” option.
The details in VisualBasic
To specify the action that the button or buttons will perform, you must use VisualBasic. This window will open from the moment you click on the “See code” button mentioned above.
- When you are in the VisualBasic window, click on the «General» bar to select the name you have assigned to the button.
- Remember that these buttons have two states: true and false. Thus, the actions to hide or show the rows and columns must be based on this when specifying the actions through Visual Basic.
So, to set the values of the buttons and carry out certain actions , you must insert the following command:
if OcultarColumna.Value = True Then
Columns (1).entireColumn.hidden = True
Columns(1).EntireColumn.hidden = False
This action will cause that, when pressing the toggle button and entering the «True» state, column A will be hidden, and when pressing again, entering the «False» state, it will be shown again.
It is important to note that the term “HideColumn” corresponds to the name that has been assigned to the button for this example. It may vary depending on the name you assign.
The above procedure served to hide a column. Now you can insert another button to perform the same procedure, only that the button configuration will be used to hide a specific row . In this sense:
If OcultarFila.Value = True Then
Rows(10).entirerows.hidden = True
Rows(10).entirerows.hidden = False