How to Use, Create, and Configure ActiveX Controls Toggle Buttons in Excel 2007 and 2010
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.
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:
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
In Excel 2010:
- Select the File menu
- Choose Options
- Select the Customize Ribbon tab
- Check Developer under Main Tabs as shown below
- 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
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:
First, decide what you want your button to do. In my first example, I want my button to toggle grid lines on and off.
- On the Developer tab, click Record Macro in the Code group
- Click OK (the Macro Name and Shortcut key are unimportant)
- Navigate to the View tab and select the Gridlines check box in the Show / Hide group
- Go back to the Developer tab and select Stop Recording
- 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
- 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
- The code that Excel 2007 and 2010 needs to activate the button is shown below:
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>
<Insert what you want Excel to do here when the button is pressed again>
- 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
ActiveWindow.DisplayGridlines = True
- 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!
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).
The code for turning split screens on is:
.SplitColumn = 5
.SplitRow = 9
To turn it off:
.SplitColumn = 0
.SplitRow = 0
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:
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:
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:
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:
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:
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.
More by this Author
- 2Use OFFSET and COUNTA to create dynamic ranges with automatically updating formulas in Excel 2007 and Excel 2010
The OFFSET and COUNTA functions allow you to create dynamic ranges of data. Formulas created to SUM this data (for example) will automatically update if data is added or removed from within the range.
Dynamic charts in Excel change automatically if data it is added or removed saving you time and effort. They use a formula with the OFFSET and COUNTA functions and named ranges using Defined Names.
If your data range contains many blank cells, it can be hard to correctly create a chart. Use the IF function to convert blank cells to #N/A which Excel ignores or change how Excel deals with blanks.
No comments yet.