The ActiveX on / off button
There are courses of different levels to learn how to use Microsoft Excel. Many 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.
What is the ActiveX on / off button for?
The on / off button it is a combination of a command button and a check box. They are typically used for status indication (yes or no, on or off), i.e. it alternates between enabled or disabled states when performing an action.
In addition to showing or hiding columns, it can also be used for display graphics and other elements found in the Excel workbook.
Insert the ActiveX toggle button
To start using this tool Microsoft Excel, the first thing to do is go to the «Developer» tab located in the program's ribbon. If this tab is not enabled, you can do the following:
- Right-click on the ribbon.
- Select the ribbon customization option.
- Check the «Developer» box.
Once this tab is enabled, go to it to be able to use the tools needed to insert the toggle button:
- Click on developer card.
- Find and select the insert option.
- A menu will appear where you will find the ActiveX controls section at the bottom.
- Among the last buttons is the toggle button option.
- Select it and start drawing it on the paper.
Button configuration
Once you've drawn the toggle button in your Excel workbook, it's time to review and configure its properties. Once you have activated the design mode that appears when you select the developer tab, 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 button name, colors, size, among other things.
- In the box " LinkedCell »You must specify the cell with which you will connect your button activation / disabling.
- When you have already assigned the corresponding name to the action that the button or buttons will perform, you can click on the "View Code" button located 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 "View 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. Therefore, the actions to hide or show the rows and columns must be based on this when specifying the actions through Visual Basic.
So for set button values and perform certain actions, you need to enter the following command:
if HideColumn.Value = True Then
Colonne (1).interaColumn.hidden = True
Other
Colonne (1).EntireColumn.hidden = False
End if
This action will cause when pressing the toggle button and entering the "True" state, column A will be hidden and when pressed again, entering the "False" state, it will be shown again.
It is important to note that the term "HideColumn" corresponds to the name that was assigned to the button for this example. It may vary depending on the name assigned.
Hide row
The above procedure was used to hide a column. You can now insert another button to perform the same procedure, only the button configuration will be used for hide a specific row. In this sense:
If HideRow.Value = True then
Righe (10).interorerows.hidden = True
Other
Righe (10).interorerows.hidden = False
End if
