Chapter 25: Spin Buttons, Scroll Bars, Option Buttons, Check Boxes, Combo Boxes, and Group List Boxes

Overview

• I need to run a sensitivity analysis that has many key inputs, such as sales, annual sales growth, price, and unit cost. Is there a way I can quickly vary these inputs and see the effect of the variation on the calculation of net present value, for example?

• How do I set up a simple check box that toggles conditional formatting on and off?

• How can I set up my worksheet so that my supply chain personnel can click a button to choose whether we charge a high, low, or medium price for a product?

• How can I create an easy way for a user of my worksheet to enter a day of the week without having to type any text?

User forms enable the Microsoft Office Excel user to add a variety of cool, useful controls to a worksheet. In this chapter, we will show you how easy it is to use spin buttons, scroll bars, check boxes, option buttons, combo boxes, and list boxes. To access Excel user forms, on the Developer tab of the Ribbon, click Insert from the Controls group to display the Forms Controls (not to be confused with the ActiveX Controls, which are usually used within the Microsoft Visual Basic for Applications (VBA) programming language.)

Note 

To display the Developer tab, click the Microsoft Office Button, and then click Excel Options. On the Popular tab, check the Show Developer Tab In The Ribbon box, and then click OK.

The user forms we will discuss are shown in Figures 25-1 through 25-7 on the next page.

Figure 25-1: Spin button

Figure 25-2: Scroll bar

Figure 25-3: Check box

Figure 25-4: Option button

Figure 25-5: Group box

Figure 25-6: Combo box

Figure 25-7: List box

We begin by showing you how to use spin buttons and scroll bars.