Consumer ElectronicsComputersCell PhonesHome Theater & AudioGraphic Design & Video EditingInternetIndustrial Technology

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

Updated on April 29, 2016

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 hub, 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. | Source

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.

I have a hub on creating and configuring Command Buttons in which I create a button that allows the user to reset all the drop down boxes on a worksheet with a single click. That hub can be found here:

http://robbiecwilson.hubpages.com/hub/Create-a-Command-Button-in-Excel-2007

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. | Source

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. | Source

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. | Source
  • 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. | Source

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!

For more information on creating and recording macros in Excel 2007 / 2010, I have a hub that covers this in greater detail:

http://robbiecwilson.hubpages.com/hub/Guide-to-recording-Macros-in-Excel-2007

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. | Source
  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. | Source

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. | Source

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. | Source

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 hub, 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.

This hub on Toggle Buttons is the latest in my series on Excel 2007 / Excel 2010’s Form and ActiveX Controls.

In addition to scroll bars, I also have a number of hubs that investigate other controls available in Excel 2007. These include:

Example of a Combo Box created in Excel 2007 and Excel 2010.
Example of a Combo Box created in Excel 2007 and Excel 2010. | Source

Combo Boxes allow use of a configurable list to create a button with a drop down which is ideal for small lists such as delivery options, months of the year etc. The hub for Combo Boxes can be found here:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Combo-boxes-in-Excel-2007

Example of a Combo Box with Conditional Formatting created in Excel 2007 and Excel 2010.
Example of a Combo Box with Conditional Formatting created in Excel 2007 and Excel 2010. | Source

Check Boxes are very useful for lists such as shopping lists or To-Do lists or for Yes / No, On / Off lists which can be illustrated by selecting or deselecting check boxes. You can also use Conditional Formatting to change the text colour based on whether the check box is selected or not. My hub for Check Boxes that can be found here:

http://robbiecwilson.hubpages.com/hub/Configuring-and-Using-Check-boxes-in-Excel-2007

Check Boxes with additional Conditional Formatting in Excel 2007 and Excel 2010.
Check Boxes with additional Conditional Formatting in Excel 2007 and Excel 2010. | Source

Conditional Formatting: Both Combo boxes and Check Boxes have much more impact when they are used alongside Conditional Formatting. It allows you to change text or cells using rules so that, as in the figures above, when a task is completed the text for that task is turned green and when it is outstanding it is red or, it has a tick (or check) next to it when complete or a cross if it is incomplete. My hub on Conditional Formatting can be found here:

http://robbiecwilson.hubpages.com/hub/Conditional-Formatting-in-Excel-2007

List Boxes: this Control allows you to create a box which contains a list that the user can select items from. Users can select one (Form Controls) or multiple items and can also use shift or control to select multiple items (ActiveX Controls). I have created hubs on both Form and ActiveX Controls List Boxes which are available here:


Form Controls:

http://robbiecwilson.hubpages.com/hub/How-to-Use-Create-and-Configure-Form-Controls-List-Boxes-in-Excel-2007-and-Excel-2010

ActiveX Controls:

http://robbiecwilson.hubpages.com/hub/Using-creating-and-configuring-ActiveX-Controls-List-Boxes-in-Excel-2007-and-Excel-2010

Example of an ActiveX Controls List Box created in Excel 2007 and Excel 2010.
Example of an ActiveX Controls List Box created in Excel 2007 and Excel 2010. | Source
Example of Form Controls List Boxes created in Excel 2007 and Excel 2010.
Example of Form Controls List Boxes created in Excel 2007 and Excel 2010. | Source

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.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Dénes 5 months ago

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