How to create, configure and use Form Controls and ActiveX Controls Spin Buttons in Excel 2007 and Excel 2010
Welcome to my latest hub for Excel 2007 and Excel 2010. Today, I will be looking at the Spin Button. Spin Buttons allow you to create a button so that users can quickly select a number from a range using an up or down arrow. In my example today, we create some spin buttons to allow users of my spreadsheet to select their date of birth.
- Form Controls Spin Buttons are simpler to configure and are recommended ahead of ActiveX buttons.
- ActiveX allows you to add Visual Basic scripts to your button and also allows you to change its colours as well as a number of other advanced features.
- ActiveX buttons are not compatible with Macs.
Creating a Form Controls Spin Button in Excel 2007 and Excel 2010
Before creating a new Spin Button, we need to ensure that the Developer tab is available in Excel. If it is not available and you use Excel 2007:
- Click on the Excel button
- Select Excel Options
- Select Show Develop tab in the Ribbon
- Click OK
If you are using Excel 2010:
- Browse to the File menu
- Select Options
- Click the Customize Ribbon tab
- Under Main Tabs tick Developer as shown below
Now that the Developer tab is visible:
- Click the Insert button in the Controls group
- Select Spin Button in the Form Controls section
- The cursor will change to a cross, select the outline of your Spin Button
Configuring a Form Controls Spin Button in Excel 2007 and Excel 2010
Now we need to configure our new Spin Button:
- Right click on it and select Format Control
- On the Control tab, first select the Minimum value. The first Spin Button will allow the user to select the day they were born so the minimum value is 1
- For Maximum value we choose 31
- Incremental change is the number that the value will increase by, which in this case is 1
- Finally, select the Cell link which is the cell that the number created by the Spin Button will appear in
- You can also select 3-D shading which adds as you would expect, 3-D shading to the button
To create additional buttons, right click your Spin Button and then Copy and Paste to create two new buttons. Configure them as above for your months and years.
To allow the results of your three spin buttons to display the user’s birth date above your buttons:
- Ensure that the Cell link option is configured correctly so they are in the correct order
- I then added a column in between each cell and added a /
- Then re-size the three new columns to the width of the /
- Next, re-size the columns containing the day, month
- Finally, I changed the alignment of the cells, using the buttons in the Alignment group on the Home tab. I left aligned the day and right aligned the month and year to bring them together
- I then added a border and re-coloured the cells by selecting them, right clicking and selecting Format Cells and changing the cells using the options on the Fill and Border tabs
Aligning your Spin-buttons in Excel 2007 and Excel 2010
When you use copy and paste (or even if you create new buttons from scratch) it is unlikely that your buttons will align exactly. To ensure that they line up perfectly:
- Hold down the Control key and left click select all the Spin Buttons you want to align.
- You will see that the buttons are all selected as below
- Select the Align button on the Page Layout tab in the Arrange group
- Use Align Top and Align Bottom to line the buttons up horizontally
- To ensure that the gaps between them are even, select Distribute Horizontally
Creating ActiveX Controls Spin Buttons in Excel 2007 and Excel 2010
Creating an ActiveX button is identical to how we created a Form Controls button, except that we choose the Spin Button in the ActiveX Controls section via the Insert button on the Developer tab in the Controls group.
Note: to configure your ActiveX button, you must be in Design mode. To enter / exit Design mode, select the Design Mode button which is next to the Insert button we used to create our Spin Button.
Now that we are in Design mode, right click on your button. There are two more options available when compared to Form Controls buttons:
Right click and select Properties which opens up some advanced options available as shown below
Most of these can be safely left as default:
- Forecolor and Backcolor change the colour of your button
- You can also change the mouse pointer or the icon using MousePointer and MouseIcon respectively.
- ViewCode allows you to add Visual Basic code to your button
- Format Control allows you to configure your button in the same manner we configured our Form Controls button
Spin buttons allow you to create buttons so that users can select numbers from a range quickly and easily. In my example today, we looked at creating three spin buttons so that users can select their birth date. We also investigated the difference between the simpler Form Controls button and the more complex ActiveX button. We also learnt how to align buttons and how to create a number of buttons quickly and easily.
Alongside this hub on Spin Buttons, I have also written a number of hubs that investigate the other controls available in Excel 2007. These include:
Combo Boxes: allow users to select options from a drop down list that is fully configurable. Combo boxes are useful for short lists such as zodiac signs or inventories. My Combo Boxes hub can be found here:
Check Boxes: such as To-Do lists or Shoping Lists or for Yes / No, On / Off lists which can be illustrated by selecting or deselecting check boxes. Conditional formatting can be used as well to change the text colour based on the result of check box. In addition, I have a hub for Check Boxes that can be found here.
Conditional Formatting: Both Combo boxes and Check Boxes are made even more useful when they are used together with Conditional Formatting. Conditional Formatting allows you to alter 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 has not yet been completed. My hub detailing how to use and Conditional Formatting can be found here:
Command Buttons: used to create a click-able button in Excel that is designed to perform a specific function using Visual Basic. In my hub, I created a reset button that resets the sheets combo boxes or check boxes to their original setting. My hub on Command buttons can be found here:
Scroll Bars: a scroll bar allows a user to select a value by a number of means. They can click the arrows, drag the slide or click on the bar itself. My hub on scroll bars can be found here:
I do hope that you enjoyed reading this hub and that you found it useful and informative. Please feel free to leave any comments you may have below. Many thanks for reading!