power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Using embedded worksheet controls

ShareThis

Though it's beyond the scope of this tutorial to go into great detail on using forms and controls, we do want to highlight their potential value in building Monte Carlo simulations. 

As highlighted by the screenshot below from the FastSlow example, a variety of control types can be used with worksheets.  (Those illustrated here are the spin button, the combo box, and the list box.)

The primary benefits of using form controls are (1) to simplify data input and (2) to restrict input to a defined set of options.  This can help reduce user error when using the spreadsheet model.

 

To insert a control using Excel 2007, click the "Insert" button under the "Developer" ribbon tab.  Select the desired control, then drag a rectangle on your worksheet where you wish the control to be placed.

"Form Controls" can be edited (assigned values, etc) by right-clicking on the control and selecting "Format control..." from the drop-down menu.

 

"ActiveX" controls can be edited by selecting "Design Mode" in the Develop Tab, then clicking "Properties" also in the Developer Tab.

 


In Excel 2003 and earlier versions, controls are selected from the Control Toolbox.  Make sure it is selected in the View - Toolbars drop-down menu.  From the Control Toolbox, insert controls in your worksheet using the same method described above.  Edit the controls by first clicking the leftmost icon on the Control Toolbox - the "Design Mode" button - then clicking the second button on the toolbox - the "Properties" button.

 

 

Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.