Updated date:

How to Use, Create, and Configure ActiveX Controls Toggle Buttons in Excel 2007 and 2010

Introduction

Hi and welcome to my latest hub on Controls in Excel 2007 and 2010. Today, I will look at the Toggle Button. Toggle Buttons are only available as ActiveX Controls and they are perfect for situations where you want to switch between two states in Excel.

Say for example, you want to be able to switch between having grid lines visible or hidden, or you want to switch between split screens and the default view then a Toggle Button will allow you to do this with a single click.

In this article, I will illustrate how to create your Toggle Button and also how to create the Visual Basic code you will need to configure it. We will create two buttons, one to turn grid lines on and off and another to switch between split screens.

ActiveX Toggle Buttons created using Excel 2007 and Excel 2010.

ActiveX Toggle Buttons created using Excel 2007 and Excel 2010.

Toggle Buttons are similar to Command Buttons in that you add Visual Basic to them both which is activated when the button is pressed. Toggle Buttons differ in that they can be configured to perform two functions, whereas a Command Button typically only performs one.

A Command Button alongside the Visual Basic code designed to reset drop down boxes with a single click created using Excel 2007 and Excel 2010.

A Command Button alongside the Visual Basic code designed to reset drop down boxes with a single click created using Excel 2007 and Excel 2010.

Creating a Toggle Button in Excel 2007 and Excel 2010

Before we start to create our Toggle Button, we need to ensure that the Developer tab is visible. If it is not present:

In Excel 2007:

  • Click the Excel button
  • Select Excel Options
  • On the Popular tab, ensure that Show Developer tab in the Ribbon is selected
  • Click OK
Adding the Developer tab in Excel 2007.

Adding the Developer tab in Excel 2007.

In Excel 2010:

  • Select the File menu
  • Choose Options
  • Select the Customize Ribbon tab
  • Check Developer under Main Tabs as shown below
Adding the Developer tab in Excel 2010.

Adding the Developer tab in Excel 2010.

  • Next, click on the Insert button on the Developer tab in the Controls group and select a Toggle Button in the ActiveX section
  • The cursor will change to a +
  • Select the outline of your Toggle Button

Configuring Toggle Buttons in Excel 2007 and Excel 2010

Now the Toggle Button has been created, we need to configure it.

Note: To configure your Toggle Button, you need to be in Design Mode. To select it, click on the Design Mode button on the Developer tab in the Controls group.

  • Select your new button
  • Right Click on the button and select Properties, or click the Properties button to the right of the Design Mode button
  • The Properties dialogue box will open
Configurable options for a Toggle Button in Excel 2007 and Excel 2010.

Configurable options for a Toggle Button in Excel 2007 and Excel 2010.

The majority of these options you can leave as default.

  • Caption – allows you to change the text displayed on your button
  • BackColor – this option alters the colour of your button

Note: If you want to change the Height and Width, I recommend you do that by right clicking your button and selecting Format Control and adjusting them on the Size tab. The Size tab shows the dimensions in centimetres which are easier to calculate than pixels.

Adding Visual Basic code to a Toggle Button in Excel 2007 and Excel 2010

The next step is to add Visual Basic to the button. If you do not know much about Visual Basic, never fear, Excel will help you create it by recording what you need in a macro!

First, decide what you want your button to do. In my first example, I want my button to toggle grid lines on and off.

  1. On the Developer tab, click Record Macro in the Code group
  2. Click OK (the Macro Name and Shortcut key are unimportant)
  3. Navigate to the View tab and select the Gridlines check box in the Show / Hide group
  4. Go back to the Developer tab and select Stop Recording
  5. Repeat steps one to four

Now we need to get the Visual Basic code from the macros. Click the Macros button on the Developer tab

  • Select the first macro and click Edit
  • The Microsoft visual Basic screen will open
Module that contains the Visual Basic code for your Toggle Button in Excel 2007 and Excel 2010.

Module that contains the Visual Basic code for your Toggle Button in Excel 2007 and Excel 2010.

  1. The two lines of code that we need are those two that begin with ActiveWindow. Copy them and then close the whole Microsoft Visual Basic window
  2. The code that Excel 2007 and 2010 needs to activate the button is shown below:
Visual Basic code that allows you to add selectable actions to your Toggle Button in Excel 2007 and Excel 2010.

Visual Basic code that allows you to add selectable actions to your Toggle Button in Excel 2007 and Excel 2010.

So that you can copy the code itself, here it is repeated:

Private Sub ToggleButton1_Click()

If ToggleButton1.Value = True Then

<Insert what you want Excel to do here when the button is first pressed>

Else

<Insert what you want Excel to do here when the button is pressed again>

End If

End Sub

  • Now we add the text to turn grid lines off and back on again

Private Sub ToggleButton1_Click()

If ToggleButton1.Value = True Then

ActiveWindow.DisplayGridlines = False

Else

ActiveWindow.DisplayGridlines = True

End If

End Sub

  • Next we add the code to the button itself
  • Select your button while in Design Mode and right click then select View Code
  • Delete all the text in the Code box and paste in your new code
  • Exit Design Mode and now test your button which should now toggle gridlines!
Complete code used to turn grid lines on / off and switch split screens on / off in two Toggle Buttons created in Excel 2007 and Excel 2010.

Complete code used to turn grid lines on / off and switch split screens on / off in two Toggle Buttons created in Excel 2007 and Excel 2010.

Creating more than one Toggle Button in Excel 2007 / Excel 2010

The process for creating any number of buttons in Excel is identical to that above:

  • Create additional buttons
  • Use a macro or write your code for what you want your button to do
  • Add your code to the generic Toggle Button code above

There is one important consideration when dealing with more than one button. If you look at my screenshot above, you can see the code for the two buttons I created (one to toggle gridlines, the other to toggle split screens).

Important considerations when creating code for more than one Toggle Button in Excel 2007 and Excel 2010.

Important considerations when creating code for more than one Toggle Button in Excel 2007 and Excel 2010.

The code for turning split screens on is:

With ActiveWindow

.SplitColumn = 5

.SplitRow = 9

End With

To turn it off:

With ActiveWindow

.SplitColumn = 0

.SplitRow = 0

End With

Conclusion

Toggle Buttons in Excel 2007 and Excel 2010 allow you to configure a button that you can use to switch between states (for example turning gridlines on or off). In today’s article, I looked at creating and configuring a Toggle Button, recording macros to capture the Visual Basic commands Excel use and adding that code to your button. I also looked at creating more than one button and investigated the pitfalls associated with multiple buttons.

Many thanks for reading and I hope that you found this hub interesting and informative. Please feel free to leave any comments you may have below.

This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.

Comments

Dénes on March 20, 2017:

accidentally I addedd an activex button to my excel file and the as a conxequence the file freezed down. How to delet it?

Related Articles