How to Use the ActiveX Toggle Button to Show and Hide Columns or Rows

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

The ActiveX toggle button

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 .

What is the ActiveX toggle button for?

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.

Insert ActiveX toggle button

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.
See also  How Much Money Can You Make on TikTok if You Succeed and Become a Famous Influencer?

Once this tab is enabled, head over to it so you can use the tools you’ll need to insert the toggle button:

  1. Click on the developer tab .
  2. Locate and select the insert option.
  3. A menu will be displayed in which you will locate, at the bottom, the ActiveX controls section.
  4. Among the last buttons is the toggle button option.
  5. Select it and start drawing it on the sheet.

Button Configuration

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.
See also  How to Set Up a Quick Access to Launch Camera on Android

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

Else

Columns(1).EntireColumn.hidden = False

End if

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.

Hide row

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

Else

Rows(10).entirerows.hidden = False

End if

Leave a Comment