Use Excel VBA to Create Spin Buttons – Really Precisely!

Use Excel VBA to Create Spin Buttons!

Click here to download the STARTER example file used in the videos.
Click here to download the COMPLETED example file used in the videos.

Spin buttons are another great feature of Excel. Spin buttons speed up the process of data input by allowing us to change the value of a cell with a single button click. Cool! Correctly implemented, they can be a great time-saving feature. But, implementation can be problematic … If you have multiple rows in your spreadsheet, it can be time-consuming to creating lots of buttons. Moreover, getting them to line up neatly can be onerous. With some simple VBA coding, however, you can speed up this task and get it done with maximum precision. Welcome to the Use Excel VBA to Create Spin Buttons video series!

Video 1

In the first video, Chris demonstrates how to create a button, and how to link the button to a cell. It is important to ‘snap’ the button to the gridlines (use the Alt key on a PC) and to ensure that row heights are consistent – this will help create a neat and tidy look.

Video 2

In the second video, we look at how to use Excel VBA to create additional spin buttons. This may sound intimidating, but a basic routine like this is actually fairly easy to create. We use the macro recorder to record and examine some VBA code, then we make some tweaks to get it working for us. Finally, we add an instruction to the Excel VBA to ensure the buttons are positioned with maximum precision.

Video 3

In the third video, we explore how to ‘scale up’ the code. Some VBA to create a single button is great, but what if we want to create multiple buttons? This involves a powerful coding concept known as a ‘loop’. A loop works through a set of instructions a certain number of times, and is exactly what we need to create and position multiple buttons. Check it out!

Video 4

A spin button only works if it is linked to a cell – the cell that changes when the spin button is clicked. Clearly, however, we do not want to click through all of the spin buttons to manually link each to a cell. This would not be in the spirit of the video series! Can we achieve this using VBA instead? Find out in the last installment of the Use Excel VBA to Create Spin Buttons video series!

Was this video series helpful for you? What have you used spin buttons for? Do you have any feedback on the video series? We’d love to hear from you. Get in touch with Tiger using the platforms below!

For regular spreadsheet hints and tips and more on the #ExcelRevolution:

This entry was posted in Advanced Articles, Excel Guidance Articles, Excel Guidance Videos & Articles, Intermediate Articles, Uncategorized. Bookmark the permalink. Comments are closed, but you can leave a trackback: Trackback URL.