Excel 2007 - Forms

Both a Form control button and an ActiveX control command button are also referred to as a push button. You might use a button or a command button to automate the printing of a worksheet, the filtering of data, or the calculation of numbers. In general, a Form control button and an ActiveX control command button are similar in appearance and function. However, they do have a few differences, which are explained in the following sections.

Button (Form control)			Command button (ActiveX control)
	Top of Page Add a button (Form control) If the Developer tab is not available, display it. Display the Developer tab Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. Note The Ribbon is a component of the Microsoft Office Fluent user interface. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Button . Click the worksheet location where you want the upper-left corner of the button to appear. Assign a macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) to the button. For more information about how to create and edit macros, see the Help section, Macros. To specify the control properties of the button, right-click the button, and then click Format Control. Top of Page Add a command button (ActiveX control) If the Developer tab is not available, display it. Display the Developer tab Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. Note The Ribbon is a component of the Microsoft Office Fluent user interface. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button . Click the worksheet location where you want the upper-left corner of the command button to appear. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode . To specify the control properties of the command button, on the Developer tab, in the Controls group, click Properties . Tip You can also right-click the command button, and then click Properties. Note Before you click Properties, make sure that the object for which you want to examine or change properties is already selected. The Properties box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following table summarizes the properties that are available. If you want to specify	Use this property

	General:
	Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)

	Whether the control can receive focus and respond to user-generated events.	Enabled (Form)

	Whether the control can be edited.	Locked (Form)

	The name of the control.	Name (Form)

	The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)

	Whether the control can be printed.	PrintObject (Excel)

	Whether the control is visible or hidden.	Visible (Form)


	Text:
	Font attributes (bold, italic, size, strikethrough, underline, and weight).	Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)

	Descriptive text on the control that identifies or describes it.	Caption (Form)

	Whether the contents of the control automatically wrap at the end of a line.	WordWrap (Form)


	Size and Position:
	Whether the size of the control automatically adjusts to display all the contents.	AutoSize (Form)

	The height or width in points.	Height, Width (Form)

	The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)


	Formatting:
	The background color.	BackColor (Form)

	The background style (transparent or opaque).	BackStyle (Form)

	The foreground color.	ForeColor (Form)

	Whether the control has a shadow.	Shadow (Excel)


	Image:
	The bitmap to display in the control.	Picture (Form)

	The location of the picture relative to its caption (left, top, right, and so on).	PicturePosition (Form)


	Keyboard and Mouse:
	The shortcut key for the control.	Accelerator (Form)

	A custom mouse icon.	MouseIcon (Form)

	The type of pointer that is displayed when the user positions the mouse over a particular object (standard, arrow, I-beam, and so on).	MousePointer (Form)

	Whether the control takes the focus when clicked.	TakeFocusOnClick (Form)

Top of Page

See Also

Excel > Forms

Add a check box, option button, or toggle button to a worksheet

Excel 2007

You can use a check box, option button, or toggle button to indicate "either/or" (or binary) choices.

What do you want to do?

Learn about check boxes, option buttons, and toggle buttons

Add a check box (Form control)

Add a check box (ActiveX control)

Add an option button (Form control)

Add an option button (ActiveX control)

Add a toggle button (ActiveX control)

Learn about check boxes, option buttons, and toggle buttons

Check box Turns on or off a value that indicates an opposite and unambiguous choice. You can select more than one check box at a time on a worksheet or in a group box. For example, you can use a check box to create an order form that contains a list of available items or in an inventory tracking application to show whether an item has been discontinued.

Check box (Form control)			Check box (ActiveX control)
Option button Allows a single choice from a limited set of mutually exclusive choices. An option button (or radio button) is usually contained in a group box or frame. For example, you can use an option button on an order form so that a user can select one of a range of sizes, such as small, medium, large, or extra large. Or you can use it for a choice of shipping options, such as ground, express, or overnight. Option button (Form control)			Option button (ActiveX control)
Toggle button Indicates a state, such as Yes/No, or a mode, such as On/Off. The button alternates between an enabled and disabled state when it is clicked. For example, you can use a toggle button to switch between design mode and edit mode, or as an alternative to a check box. Note The toggle button is not available as a Form control, only as an ActiveX control. Toggle button (ActiveX control)
	Top of Page Add a check box (Form control) If the Developer tab is not available, display it. Display the Developer tab Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. Note The Ribbon is a component of the Microsoft Office Fluent user interface. On the Developer tab, in the Controls group, click Insert and then, under Form Controls, click Check box . Click the worksheet location where you want the upper-left corner of the control to appear. On the Developer tab, in the Controls group, click Properties . Tip You can also right-click the control, and then click Format Control. To specify the control properties, do the following: Under Value, specify the initial state of the check box by doing one of the following: To display a check box that is filled with a check mark, click Checked. A check mark indicates that the check box is selected. To display a check box that is cleared, click Unchecked. To display a check box that is filled with shading, click Mixed. Shading indicates a combination of selected and cleared states; for example, when there is a multiple selection. In the Cell link box, enter a cell reference that contains the current state of the check box: When the check box is selected, the linked cell returns a TRUE value. When the check box is cleared, the linked cell returns a FALSE value. Note When the linked cell is empty, Microsoft Office Excel interprets the check box state as FALSE. If the check box state is mixed, the linked cell returns a #N/A error value. Use the returned value in a formula to respond to the current state of the check box. For example, a travel survey form contains two check boxes labeled Europe and Australia in a Places traveled group box. These two check boxes are linked to cells C1 (for Europe) and C2 (for Australia). When a user selects the Europe check box, the following formula in cell D1 evaluates to "Traveled in Europe": =IF(C1=TRUE,"Traveled in Europe","Never traveled in Europe") When a user clears the Australia check box, the following formula in cell D2 evaluates to "Never traveled in Australia": =IF(C2=TRUE,"Traveled in Australia","Never traveled in Australia") If you have three states to evaluate (Checked, Unchecked, and Mixed) in the same group of options, you can use the CHOOSE or LOOKUP functions similarly. Note The size of the check box inside the control and its distance from its associated cannot be adjusted Top of Page Add a check box (ActiveX control) If the Developer tab is not available, display it. Display the Developer tab Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. Note The Ribbon is a component of the Microsoft Office Fluent user interface. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Check Box . Click the worksheet location where you want the upper-left corner of the check box to appear. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode . To specify the control properties, on the Developer tab, in the Controls group, click Properties . Tip You can also right-click the control, and then click Properties. The Properties dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available. Summary of properties by functional categories If you want to specify	Use this property
	General:
	Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)
	Whether the control can receive the focus and respond to user-generated events.	Enabled (Form)
	Whether the control can be edited.	Locked (Form)
	The name of the control.	Name (Form)
	The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)
	Whether the control can be printed.	PrintObject (Excel)
	Whether the control is visible or hidden.	Visible (Form)
	Text:
	The position of the control relative to its caption (left or right).	Alignment (Form)
	Font attributes (bold, italic, size, strikethrough, underline, and weight).	Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)
	Descriptive text on the control that identifies or describes it.	Caption (Form)
	How text is aligned in the control (left, center, or right).	TextAlign (Form)
	Whether the contents of the control automatically wrap at the end of a line.	WordWrap (Form)
	Data and binding:
	The range that is linked to the control's value.	LinkedCell (Excel)
	The content or state of the control.	Value (Form)
	Size and position:
	Whether the size of the control automatically adjusts to display all the contents.	AutoSize (Form)
	The height or width in points.	Height, Width (Form)
	The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)
	Formatting:
	The background color.	BackColor (Form)
	The background style (transparent or opaque).	BackStyle (Form)
	The foreground color.	ForeColor (Form)
	Whether the control has a shadow.	Shadow (Excel)
	The visual appearance of the border (flat, raised, sunken, etched, or bump).	SpecialEffect (Form)
	Image:
	The bitmap to display in the control.	Picture (Form)
	The location of the picture relative to its caption (left, top, right, and so on).	PicturePosition (Form)
	Keyboard and mouse:
	The shortcut key for the control.	Accelerator (Form)
	A custom mouse icon.	MouseIcon (Form)
	The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam).	MousePointer (Form)
	Specific to check box:
	A group of mutually exclusive option buttons.	GroupName (Form)
	Whether a user can specify the Null state for the control from the user interface.	TripleState (Form)

Note The size of the check box inside the control and its distance from its associated text cannot be adjusted.

Top of Page

Add an option button (Form control)

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Option button .

Click the worksheet location where you want the upper-left corner of the option button to appear.
On the Developer tab, in the Controls group, click Properties .

Tip You can also right-click the control, and then click Format Control.

To set the control properties, do the following:

Under Value, specify the initial state of the option button by doing one of the following:
- To display an option button that is selected, click Checked.
- To display an option button that is cleared, click Unchecked.
In the Cell link box, enter a cell reference that contains the current state of the option button.

The linked cell returns the number of the selected option button in the group of options. Use the same linked cell for all options in a group. The first option button returns a 1, the second option button returns a 2, and so on. If you have two or more option groups on the same worksheet, use a different linked cell for each option group.

Use the returned number in a formula to respond to the selected option.

For example, a personnel form, with a Job type group box, contains two option buttons labeled Full-time and Part-time linked to cell C1. After a user selects one of the two options, the following formula in cell D1 evaluates to "Full-time" if the first option button is selected or "Part-time" if the second option button is selected.

=IF(C1=1,"Full-time","Part-time")

If you have three or more options to evaluate in the same group of options, you can use the CHOOSE or LOOKUP functions in a similar manner.

Notes

You can also edit the control properties by selecting the control, and then by clicking Control Properties on the Forms toolbar.
The size of the option button inside the control and its distance from its associated text cannot be adjusted.

Note To see an example of option buttons that are used in an Excel template, see the help topic, Electoral College calculator.

Top of Page

Add an option button (ActiveX control)

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert and then, under ActiveX Controls, click Option Button .

Click the worksheet location where you want the upper-left corner of the option button to appear.
To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .
To set the control properties, on the Developer tab, in the Controls group, click Properties .

Tip You can also right-click the control, and then click Properties.

The Properties dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available.

Summary of properties by functional categories

If you want to specify	Use this property
General:
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)
The name of the control.	Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)
Whether the control can be edited.	Locked (Form)
Whether the control can be printed.	PrintObject (Excel)
Whether the control can receive focus and respond to user-generated events.	Enabled (Form)
Whether the control is visible or hidden.	Visible (Form)
Text:
Descriptive text on the control that identifies or describes it.	Caption (Form)
Font attributes (bold, italic, size, strikethrough, underline, and weight).	Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)
How text is aligned in the control (left, center, or right).	TextAlign (Form)
The position of the control relative to its caption (left or right).	Alignment (Form)
Whether the contents of the control automatically wrap at the end of a line.	WordWrap (Form)
Data and binding:
The content or state of the control.	Value (Form)
The range linked to the control's value.	LinkedCell (Excel)
Size and position:
The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)
The height or width in points.	Height, Width (Form)
Whether the size of the control automatically adjusts to display all the contents.	AutoSize (Form)
Formatting:
The background color.	BackColor (Form)
The background style (transparent or opaque).	BackStyle (Form)
The foreground color.	ForeColor (Form)
The visual appearance of the border (flat, raised, sunken, etched, or bump).	SpecialEffect (Form)
Whether the control has a shadow.	Shadow (Excel)
Image:
The bitmap to display in the control.	Picture (Form)
The location of the picture relative to its caption (left, top, right, and so on).	PicturePosition (Form)
Keyboard and mouse:
A custom mouse icon.	MouseIcon (Form)
The shortcut key for the control.	Accelerator (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam).	MousePointer (Form)
Specific to option button:
A group of mutually exclusive option buttons.	GroupName (Form)
Whether a user can specify the Null state for the control from the user interface.	TripleState (Form)

Note The size of the option button inside the control and its distance from its associated text cannot be adjusted.

Top of Page

Add a Toggle button (ActiveX control)

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Toggle Button .

Click the worksheet location where you want the upper-left corner of the toggle button to appear.
To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .
To set the control properties, on the Developer tab, in the Controls group, click Properties .

Tip You can also right-click the control, and then click Properties.

The Properties dialog box is displayed. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available.

If you want to specify	Use this property
General:
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)
Whether the control can be edited.	Locked (Form)
The name of the control.	Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)
Whether the control can be printed.	PrintObject (Excel)
Whether the control can receive focus and respond to user-generated events.	Enabled (Form)
Whether the control is visible or hidden.	Visible (Form)
Text:
Descriptive text on the control that identifies or describes it.	Caption (Form)
How text is aligned in the control (left, center, or right).	TextAlign (Form)
Whether the contents of the control automatically wrap at the end of a line.	WordWrap (Form)
Data and Binding:
The range linked to the control's value.	LinkedCell (Excel)
The content or state of the control.	Value (Form)
Size and Position:
Whether the size of the control automatically adjusts to display all the contents.	AutoSize (Form)
The height or width in points.	Height, Width (Form)
The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)
Formatting:
The background color.	BackColor (Form)
The background style (transparent or opaque).	BackStyle (Form)
The foreground color.	ForeColor (Form)
Whether the control has a shadow.	Shadow (Excel)
Image:
The bitmap to display in the control.	Picture (Form)
The location of the picture relative to its caption (left, top, right, and so on).	PicturePosition (Form)
Keyboard and Mouse:
The shortcut key for the control.	Accelerator (Form)
A custom mouse icon.	MouseIcon (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam).	MousePointer (Form)
Specific to Toggle Button:
Whether a user can specify the Null state for the control from the user interface.	TripleState (Form)

Top of Page

See Also

Excel > Forms

Add a group box or frame control to a worksheet

Excel 2007

You can use a group box or a frame control to group related controls (such as option buttons, check boxes, or closely related contents) into one visual unit.

What do you want to do?

Learn about group boxes and frame controls

Add a group box (Form control)

Add a Frame control (ActiveX control)

Learn about group boxes and frame controls

Group boxes and frame controls are rectangular objects with optional labels. Use a group box or a frame control to visually organize related items on a form. For example, in a customer order application, group the name, address, and account number of a customer. Or in an order form, group a list of available items.

Group box (Form control)			Frame control (ActiveX control)
	Top of Page Add a group box (Form control) If the Developer tab is not available, display it. Display the Developer tab Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. Note The Ribbon is a component of the Microsoft Office Fluent user interface. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Group box . Click the worksheet location where you want the upper-left corner of the group box to appear. Place related controls inside the boundary of the Group box. For example: Check boxes, such as a list of related products for purchase. Labels and text boxes, such as name and address information. Option buttons that indicate a set of mutually exclusive choices, such as Small, Medium, or Large. To specify the control properties, right-click the control, and then click Format Control. Top of Page Add a frame control (ActiveX control) If the Developer tab is not available, display it. Display the Developer tab Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. Note The Ribbon is a component of the Microsoft Office Fluent user interface. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click More Controls . In the More Controls dialog box, select Microsoft Forms 2.0 Frame from the list of available controls on your computer. Click the worksheet location where you want the upper-left corner of the frame control to appear. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode . To specify the control properties, on the Developer tab, in the Controls group, click Properties . Tip You can also right-click the control, and then click Properties. The Properties dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available. Summary of properties by functional categories If you want to specify	Use this property
	General:
	Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)
	Whether the control can receive the focus and respond to user-generated events.	Enabled (Form)
	Whether the control can be edited.	Locked (Form)
	The name of the control.	Name (Form)
	The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)
	Whether the control can be printed.	PrintObject (Excel)
	Whether the control is visible or hidden.	Visible (Form)
	Text:
	Font attributes (bold, italic, size, strikethrough, underline, and weight).	Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)
	Descriptive text on the control that identifies or describes it.	Caption (Form)
	Size and position:
	The height or width in points.	Height, Width (Form)
	The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)
	Formatting:
	The background color.	BackColor (Form)
	The color of the border.	BorderColor (Form)
	The foreground color.	ForeColor (Form)
	Whether the control has a shadow.	Shadow (Excel)
	The visual appearance of the border (flat, raised, sunken, etched, or bump).	SpecialEffect (Form)
	Image:
	The bitmap to display in the control.	Picture (Form)
	The location of a background picture (top left, top right, center, and so on).	PictureAlignment (Form)
	How to display the background picture on the control (crop, stretch, or zoom).	PictureSizeMode (Form)
	Whether you want to tile multiple copies of the picture in the control.	PictureTiling (Form)
	Keyboard and mouse:
	A custom mouse icon.	MouseIcon (Form)
	The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam).	MousePointer (Form)
	Specific to frame:
	The type of border used, either a single-line or none.	BorderStyle (Form)
	Whether the control has vertical scroll bars, horizontal scroll bars, or both.	ScrollBars (Form)
	The action to take when the user leaves the last control on a frame or page (all forms or current form).	Cycle (Form)
	Whether scroll bars remain visible when not required.	KeepScrollBarsVisible (Form)
	The height or width, in points, of the total area that can be viewed by moving the scroll bars.	ScrollHeight, ScrollWidth (Form)
	The distance, in points, of the left or top edge of the logical form from the left or top edge of the frame.	ScrollLeft, ScrollTop (Form)
	How much to change the size of an image in the frame.	Zoom (Form)

Notes

If you plan to use the frame control frequently, add it to a "Saved controls" worksheet, and then save the worksheet for reuse. The next time that you need the frame control, open that worksheet, and then copy the frame control to the worksheet form that you are editing.
All option buttons in a frame are mutually exclusive by default.
You can also use a toggle button inside a frame to select one or more items from a related group. For example, you can create an order form that contains a list of available items with a toggle button preceding each item.

Top of Page

See Also

Excel > Forms

Add a label or text box to a worksheet

Excel 2007

You use a label and a text box together for basic data entry.

What do you want to do?

Learn about labels and text boxes

Add a label (Form control)

Add a label (ActiveX control)

Add a text box (ActiveX control)

Learn about labels and text boxes

A label is text that identifies the purpose of a cell or text box or that displays descriptive text, such as titles, captions, or brief instructions. In addition, a label can display a descriptive picture. Use a label for flexible placement of instructions, to emphasize text, and when merged cells or a specific cell location is not a practical solution.

A text box is a rectangular box in which you can view, enter, or edit text or data that is bound to a cell. A text box can also be a static text field that presents read-only information. Use a text box as an alternative to entering text in a cell, when you want to display an object that floats freely. You can also use a text box to display or view text that is independent of row and column boundaries, preserving the layout of a grid or table of data on the worksheet.

Label (Form control)			Label (ActiveX control)	Text Box (ActiveX control)
	Top of Page Add a label (Form control) If the Developer tab is not available, display it. Display the Developer tab Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. Note The Ribbon is a component of the Microsoft Office Fluent user interface. On the Developer tab, in the Controls group, click Insert and then, under Form Controls, click Label . Click the worksheet location where you want the upper-left corner of the label to appear. To specify the control properties, right-click the control, and then click Format Control. Top of Page Add a label (ActiveX control) If the Developer tab is not available, display it. Display the Developer tab Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. Note The Ribbon is a component of the Microsoft Office Fluent user interface. On the Developer tab, in the Controls group, click Insert and then, under ActiveX Controls, click Label . Click the worksheet location where you want the upper-left corner of the label to appear. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode . Click the worksheet location where you want the upper-left corner of the label to appear. To specify the control properties, on the Developer tab, in the Controls group, click Properties . Tip You can also right-click the label, and then click Properties. The Properties dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available. Summary of properties by functional categories If you want to specify	Use this property
	General:
	Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)
	Whether the control can receive the focus and respond to user-generated events.	Enabled (Form)
	Whether the control can be edited.	Locked (Form)
	The name of the control.	Name (Form)
	The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)
	Whether the control can be printed.	PrintObject (Excel)
	Whether the control is visible or hidden.	Visible (Form)
	Text:
	Font attributes (bold, italic, size, strikethrough, underline, and weight).	Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)
	Descriptive text on the control that identifies or describes it.	Caption (Form)
	How text is aligned in the control (left, center, or right).	TextAlign (Form)
	Whether the contents of the control automatically wrap at the end of a line.	WordWrap (Form)
	Size and position:
	Whether the size of the control automatically adjusts to display all contents.	AutoSize (Form)
	The height or width in points.	Height, Width (Form)
	The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)
	Formatting:
	The background color.	BackColor (Form)
	The background style (transparent or opaque).	BackStyle (Form)
	The color of the border.	BorderColor (Form)
	The type of border (none or single-line).	BorderStyle (Form)
	The foreground color.	ForeColor (Form)
	Whether the control has a shadow.	Shadow (Excel)
	The visual appearance of the border (flat, raised, sunken, etched, or bump).	SpecialEffect (Form)
	Image:
	The bitmap to display in the control.	Picture (Form)
	The location of the picture relative to its caption (left, top, right, and so on).	PicturePosition (Form)
	Keyboard and mouse:
	The shortcut key for the control.	Accelerator (Form)
	A custom mouse icon.	MouseIcon (Form)
	The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam).	MousePointer (Form)

Top of Page

Add a text box (ActiveX control)

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Text Box .

Click the worksheet location where you want the upper-left corner of the text box to appear.
To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .
To specify the control properties, on the Developer tab, in the Controls group, click Properties .

Tip You can also right-click the text box, and then click Properties.

Summary of properties by functional categories

If you want to specify	Use this property
General:
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)
Whether the control can receive the focus and respond to user-generated events.	Enabled (Form)
Whether the control can be edited.	Locked (Form)
The name of the control.	Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)
Whether the control can be printed.	PrintObject (Excel)
Whether the control is visible or hidden.	Visible (Form)
Text:
Whether a word or a character is the basic unit used to extend a selection.	AutoWordSelect (Form)
Font attributes (bold, italic, size, strikethrough, underline, and weight).	Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)
Whether selected text remains highlighted when the control does not have the focus.	HideSelection (Form)
The default run time mode of the Input Method Editor (IME).	IMEMode (Form)
Whether the size of the control adjusts to display full or partial lines of text.	IntegralHeight (Form)
The maximum number of characters a user can enter.	MaxLength (Form)
Whether the control supports multiple lines of text.	MultiLine (Form)
Placeholder characters, such as an asterisk (*), to be displayed instead of actual characters.	PasswordChar (Form)
Whether the user can select a line of text by clicking to the left of the text.	SelectionMargin (Form)
The text in the control.	Text (Form)
How text is aligned in the control (left, center, or right).	TextAlign (Form)
Whether the contents of the control automatically wrap at the end of a line.	WordWrap (Form)
Data and binding:
The range that is linked to the control's value.	LinkedCell (Excel)
The content or state of the control.	Value (Form)
Size and position:
Whether the size of the control automatically adjusts to display all the contents.	AutoSize (Form)
The height or width in points.	Height, Width (Form)
The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)
Formatting:
The background color.	BackColor (Form)
The background style (transparent or opaque).	BackStyle (Form)
The color of the border.	BorderColor (Form)
The type of border (none or a single-line).	BorderStyle (Form)
The foreground color.	ForeColor (Form)
Whether the control has a shadow.	Shadow (Excel)
The visual appearance of the border (flat, raised, sunken, etched, or bump).	SpecialEffect (Form)
Whether an automatic tab occurs when a user enters the maximum allowable characters into the control.	AutoTab (Form)
Keyboard and mouse:
Whether drag-and-drop is enabled.	DragBehavior (Form)
The selection behavior when entering the control (select all or do not select).	EnterFieldBehavior (Form)
The effect of pressing ENTER (create a new line or move focus).	EnterKeyBehavior (Form)
A custom mouse icon.	MouseIcon (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam).	MousePointer (Form)
Whether tabs are allowed in the edit region.	TabKeyBehavior (Form)
Specific to Text Box:
Whether the control has vertical scroll bars, horizontal scroll bars, or both.	ScrollBars (Form)

Notes

To create a text box with a set of placeholder characters that accepts a password, use the PasswordChar property. Make sure that you protect the linked cell or other location in which the text is stored.

Security

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

To create a scrolling, multiple-line text box with horizontal and vertical scroll bars, set MultiLine to True, AutoSize and WordWrap to False, ScrollBars to 3, and LinkedCell to the cell address (such as D1) that you want to contain the text. To enter a new line, the user must press either CTRL+ENTER or SHIFT+ENTER, which generates a special character that is stored in the linked cell.

Top of Page

See Also

Excel > Forms

Add a list box or combo box to a worksheet

Excel 2007

You use a list box or a combo box, which are similar but slightly different controls, to let users make multiple choices of items or to enter their own value in a list. Typical examples of items in these types of lists are employee names, exchange rates, and product items.

What do you want to do?

Learn about list boxes and combo boxes

Add a list box (Form control)

Add a list box (ActiveX control)

Add a combo box (Form control)

Add a combo box (ActiveX control)

Learn about list boxes and combo boxes

List box Displays a list of one or more items of text from which a user can choose.

List box (Form control)			List box (ActiveX control)
Combo box Combines a text box with a list box to create a drop-down list box. A combo box is more compact than a list box, but it requires the user to click the down arrow to display the list of items. Use a combo box to enable a user either to type an entry or to choose only one item from a list. The control displays the current value in the text box, regardless of how that value was entered. Combo box (Form control)			Combo box (ActiveX control)
	Top of Page Add a list box (Form control) If the Developer tab is not available, display it. Display the Developer tab Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. Note The Ribbon is a component of the Microsoft Office Fluent user interface. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click List box . Click the worksheet location where you want the upper-left corner of the list box to appear. On the Developer tab, in the Controls group, click Properties . Tip You can also right-click the control, and then click Format Control. To specify the control properties, do the following: In the Input range box, enter a cell reference to a range that contains the values to display in the list box. In the Cell link box, enter a cell reference that contains the list box selection. The linked cell returns the number of the selected item in the list box. The first item in the range returns a value of 1, the second item in the range returns a value of 2, and so on. Use this number in a formula to return the actual item from the input range. For example, a dessert preference form has a list box that is linked to cell C1, the input range for the list is D1:D5, and the items in the range are: "Ice Cream" (D1), "Cake" (D2), "Liqueur" (D3), "Candy" (D4), and "Chocolate" (D5). The following formula, entered in cell B1, returns the value "Liqueur" from range D1:D5 if the value of C1 is 3, based on the current selection in the list box. =INDEX(D1:D5,C1) Under Selection type, specify how items can be selected in the list box by doing one of the following: To create a single-selection list box, click Single. To create a multiple-selection list box, click Multi. To create an extended-selection list box, click Extend. Note If you set the selection type to Multi or Extend, the cell that is specified in the Cell link box returns a value of 0 and is ignored. The Multi and Extend selection types require the use of Microsoft Visual Basic for Applications (VBA) code. In these cases, consider using the ActiveX list box control. Top of Page Add a list box (ActiveX control) If the Developer tab is not available, display it. Display the Developer tab Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. Note The Ribbon is a component of the Microsoft Office Fluent user interface. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click List Box . Click the worksheet location where you want the upper-left corner of the list box to appear. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode . To specify the control properties, on the Developer tab, in the Controls group, click Properties . Tip You can also right-click the control, and then click Properties. The Properties dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available. Summary of properties by functional categories If you want to specify	Use this property
	General:
	Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)
	Whether the control can receive the focus and respond to user-generated events.	Enabled (Form)
	Whether the control can be edited.	Locked (Form)
	The name of the control.	Name (Form)
	The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)
	Whether the control can be printed.	PrintObject (Excel)
	Whether the control is visible or hidden.	Visible (Form)
	Text:
	Font attributes (bold, italic, size, strikethrough, underline, and weight).	Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)
	The default run time mode of the Input Method Editor (IME).	IMEMode (Form)
	Whether the size of the control adjusts to display full or partial lines of text.	IntegralHeight (Form)
	Whether multiple selections of items are permitted.	MultiSelect (Form)
	The text in the control.	Text (Form)
	How text is aligned in the control (left, center, or right).	TextAlign (Form)
	Data and Binding:
	The range that is linked to the control's value.	LinkedCell (Excel)
	The content or state of the control.	Value (Form)
	Size and Position:
	The height or width in points.	Height, Width (Form)
	The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)
	Formatting:
	The background color.	BackColor (Form)
	The color of the border.	BorderColor (Form)
	The type of border (none or single-line).	BorderStyle (Form)
	The foreground color.	ForeColor (Form)
	Whether the control has a shadow.	Shadow (Excel)
	The visual appearance of the border (flat, raised, sunken, etched, or bump).	SpecialEffect (Form)
	Keyboard and Mouse:
	A custom mouse icon.	MouseIcon (Form)
	The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam).	MousePointer (Form)
	Specific to List Box:
	The source of data for multiple columns.	BoundColumn (Form)
	The number of columns to display.	ColumnCount (Form)
	A single row as a column heading.	ColumnHeads (Form)
	The width of each column.	ColumnWidths (Form)
	The range that is used to populate the list.	ListFillRange (Excel)
	The list style (plain, with option buttons, or with check boxes).	ListStyle (Form)
	How the control searches its list while the user types (first letter, complete entry, or none)	MatchEntry (Form)
	The column to store in the Text property when the user selects a row.	TextColumn (Form)
	The item that appears in the topmost position in the list.	TopIndex (Form)

Notes

To create a list box with multiple selection or extended-selection enabled, use the MultiSelect property. In this case, the LinkedCell property returns a #N/A value. You must use VBA code to process the multiple selections.
To create a two-column list box with column headers, set ColumnCount to 2, ColumnHeads to True, ColumnWidths to the width that you want for each column (for example, 72pt;72pt), ListFillRange to the range that is used to populate the list (for example, B2:C6), BoundColumn to either 1 or 2 to indicate which column value to save, and LinkedCell to a cell address that contains the selected value. By default, the column label is used as the column header (for example, Column B and Column C). To use your own column headers, place them immediately above the first value specified in ListFillRange (for example, B1 and C1) before you close the Properties dialog box. Finally, resize the list box to display both columns.
To create a list box that displays one value in the list box but saves another value in the linked cell, create a two-column list box, and then hide one of the columns by setting its ColumnWidths value to 0. For example, you can set up a two-column list box that contains the names of holidays in one column and dates associated with the holidays in a second column. To present the holiday names to users, specify the first column as the TextColumn. To store the dates of the holidays, specify the second column as the BoundColumn. To hide the dates of the holidays, set the ColumnWidths property of the second column to 0.

Top of Page

Add a combo box (Form control)

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Combo box .

Click the worksheet location where you want the upper-left corner of the combo box to appear.

The drop-down arrow is displayed with the text box collapsed.

To display the text box, drag the left-center sizing handle to the right.
On the Developer tab, in the Controls group, click Properties .

Tip You can also right-click the control, and then click Format Control.

To specify the control properties, do the following:

In the Input range box, enter a cell reference to a range that contains the values to display in the drop-down list of the combo box.
In the Cell link box, enter a cell reference that contains the selection in the drop-down list of the combo box.

The linked cell returns the number of the selected item in the drop-down list of the combo box. The first item in the range returns a value of 1, the second item in the range returns a value of 2, and so on.

Use this number in a formula to return the actual item from the input range. For example, a dessert preference form has a combo box linked to cell C1, the input range for the list is D1:D5, and the items in the range are: "Ice Cream" (D1), "Cake" (D2), "Liqueur" (D3), "Candy" (D4), and "Chocolate" (D5). The following formula, entered in cell B1, returns the value "Liqueur" from range D1:D5 if the value of C1 is 3, based on the current selection in the combo box.

=INDEX(D1:D5,C1)

Note If you want to create a combo box that enables the user to edit the text in the text box, consider using the ActiveX Combo Box control.

In the Drop-down lines box, enter the number of lines to display in the drop-down list of the combo box. If the value is:
- 0, it is ignored and treated as 1.
- Less than the number of items in the range specified in the Input range box, a scroll bar is displayed.
- Equal to or greater than the number of items in the range specified in the Input range box, no scroll bar is displayed.

Top of Page

Add a combo box (ActiveX control)

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Combo Box .

Click the worksheet location where you want the upper-left corner of the combo box to appear.
To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .
To specify the control properties, on the Developer tab, in the Controls group, click Properties .

Tip You can also right-click the control, and then click Properties.

Summary of properties by functional categories

If you want to specify	Use this property
General:
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)
Whether the control can receive the focus and respond to user-generated events.	Enabled (Form)
Whether the control can be edited.	Locked (Form)
The name of the control.	Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)
Whether the control can be printed.	PrintObject (Excel)
Whether the control is visible or hidden.	Visible (Form)
Text:
Whether a word or a character is the basic unit used to extend a selection.	AutoWordSelect (Form)
Font attributes (bold, italic, size, strikethrough, underline, and weight).	Bold, Italic, Size, StrikeThrough, Underline, Weight (Form)
Whether selected text remains highlighted when the control does not have the focus.	HideSelection (Form)
The default run time mode of the Input Method Editor (IME).	IMEMode (Form)
The maximum number of characters a user can enter.	MaxLength (Form)
Whether the user can select a line of text by clicking to the left of the text.	SelectionMargin (Form)
The text in the control.	Text (Form)
How text is aligned in the control (left, center, or right).	TextAlign (Form)
Data and binding:
The range that is linked to the control's value.	LinkedCell (Excel)
The content or state of the control.	Value (Form)
Size and position:
Whether the size of the control automatically adjusts to display all the contents.	AutoSize (Form)
The height or width in points.	Height, Width (Form)
The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)
Formatting:
The background color.	BackColor (Form)
The background style (transparent or opaque).	BackStyle (Form)
The color of the border.	BorderColor (Form)
The type of border (none or single-line).	BorderStyle (Form)
The foreground color.	ForeColor (Form)
Whether the control has a shadow.	Shadow (Excel)
The visual appearance of the border (flat, raised, sunken, etched, or bump).	SpecialEffect (Form)
Keyboard and mouse:
Whether an automatic tab action occurs after a user has entered the maximum number of characters for the control.	AutoTab (Form)
Whether drag-and-drop is enabled.	DragBehavior (Form)
The selection behavior when entering the control (select all or do not change).	EnterFieldBehavior (Form)
A custom mouse icon.	MouseIcon (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam).	MousePointer (Form)
Specific to combo box:
The source of data for multiple columns.	BoundColumn (Form)
The number of columns to be displayed.	ColumnCount (Form)
A single row as a column heading.	ColumnHeads (Form)
The width of each column.	ColumnWidths (Form)
The symbol that is displayed on the drop button (down arrow, plain, ellipsis, or underscore).	DropButtonStyle (Form)
The range that is used to populate the list.	ListFillRange (Excel)
The maximum number of rows to display in the list.	ListRows (Form)
The list style (plain, with option buttons, or with check boxes).	ListStyle (Form)
The width of the list.	ListWidth (Form)
How the control searches its list while the user types (first letter, complete entry, or none)	MatchEntry (Form)
Whether a value entered as text must match an entry in the existing list.	MatchRequired (Form)
When to show the drop button (never, with focus, or always).	ShowDropButtonWhen (Form)
How the user chooses or sets the value (drop-down combo or drop-down list).	Style (Form)
The column to store in the Text property when the user selects a row.	TextColumn (Form)
The item that appears in the topmost position in the list.	TopIndex (Form)

Notes

To create a two-column combo box with column headers, set ColumnCount to 2, ColumnHeads to True, ColumnWidths to the width that you want for each column (for example, 72pt;72pt), ListFillRange to the range that is used to populate the list (for example, B1:C6), BoundColumn to either 1 or 2 to indicate which column value to save, TextColumn to the column of data that you want displayed in the text box section of the combo box (which can be the same as or different from the BoundColumn value), and LinkedCell to a cell address that will contain the selected value. By default, the column label is used as the column header (for example, Column B and Column C). To use your own column headers, place them immediately above the first value specified in ListFillRange (for example, B1 and C1) before you close the Properties dialog box. Finally, resize the combo box to display both columns.
To create a combo box that displays one value in the combo box but saves another in the linked cell, create a two-column combo box, and then hide one of the columns by setting its ColumnWidths value to 0. For example, you can set up a two-column combo box that contains the names of holidays in one column and associated dates for the holidays in a second column. To present the holiday names to users, specify the first column as the TextColumn. To store the dates of the holidays, specify the second column as the BoundColumn. To hide the dates of the holidays, set the ColumnWidths property of the second column to 0.
To create a combo box that does not enable the user to enter new values, set Style to 2. To create a combo box that enables a user to enter new values that are not found in the list, set Style to 1, which is the default. In this case, you must write VBA code if you want to dynamically update the list values.

Top of Page

See Also

Excel > Forms

Add a scroll bar or spin button to a worksheet

Excel 2007

You use a scroll bar or spin button to quickly enter or change a range of values.

What do you want to do?

Learn about scroll bars and spin buttons

Add a scroll bar (Form control)

Add a scroll bar (ActiveX control)

Add a spin button (Form control)

Add a spin button (ActiveX control)

Learn about scroll bars and spin buttons

Scroll bar Scrolls through a range of values when you click the scroll arrows or when you drag the scroll box. You can move through a page (a preset interval) of values by clicking the region between the scroll box and either scroll arrow. Typically, a user can also type a text value directly in the associated cell or text box. Use a scroll bar for setting or adjusting a large range of values, or for cases when precision is not important. For example, use a scroll bar for a range of percentages that are estimates, or for adjusting color selection in a graduated way.

Scroll bar (Form control)			Scroll bar (ActiveX control)
Spin button Makes it easier to increase or decrease a value, such as a number increment, time, or date. To increase the value, click the up arrow; to decrease the value, click the down arrow. A user can also type a text value directly in the associated cell or text box. Use a spin button, for example, to make it easier to enter a month, day, year number, or to increase a volume level. Spin button (Form control)				Spin button (ActiveX control)
	Top of Page Add a scroll bar (Form control) If the Developer tab is not available, display it. Display the Developer tab Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK. Note The Ribbon is a component of the Microsoft Office Fluent user interface. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Scroll bar . Click the worksheet location where you want the upper-left corner of the scroll bar to appear. Note The scroll bar is added in a top-down orientation. To orient the scroll bar from left to right, drag one of the sizing handles in a diagonal direction. On the Developer tab, in the Controls group, click Properties . Tip You can also right-click the control, and then click Format Control. Note Before you click Properties, make sure that the object for which you want to examine or change properties is already selected. To specify the control properties, do the following: In the Current value box, enter the initial value in the range of allowed values below that corresponds to the position of the scroll box in the scroll bar. This value must not be: Less than the Minimum value; otherwise, the Minimum value is used. Greater than the Maximum value; otherwise, the Maximum value is used. In the Minimum value box, enter the lowest value that a user can specify by positioning the scroll box closest to the top of a vertical scroll bar or the left end of a horizontal scroll bar. In the Maximum value box, enter the largest value that a user can specify by positioning the scroll box farthest from the top of a vertical scroll bar or the right end of a horizontal scroll bar. In the Incremental change box, enter the amount that the value increases or decreases and the degree to which the scroll box moves when the arrow at either end of the scroll bar is clicked. In the Page change box, enter the amount that the value increases or decreases and the degree to which the scroll box moves when you click the area between the scroll box and either of the scroll arrows. For example, in a scroll box with a minimum value of 0 and a maximum value of 10, if you set the Page change property to 2, the value will increase or decrease by 2 (in this case, 20% of the value range of the scroll box) when you click the area between the scroll box and either of the scroll arrows. In the Cell link box, enter a cell reference that contains the current position of the scroll box. The linked cell returns the current value corresponding to the position of the scroll box. Use this value in a formula to respond to the value of the cell specified in the Cell link box that corresponds to the current position of the scroll box. For example, if you create a risk factor scroll bar with the following properties: Property	Value
	Current value	100
	Minimum value	0
	Maximum value	100
	Incremental change	1
	Page change	5
	Cell link	C1

With these settings, the user can use the scroll bar to enter a precise number or click the area between the scroll bar and arrow to change the value in increments of 5.

The following formula in cell D1 returns the exact value that is based on the current value in the linked cell:

=IF(C1 > 50, "Acceptable", "Unacceptable")

The following array formula in cell D1 assigns a grade to the risk factor, based on the current value in the linked cell.

=LOOKUP(A4,{0,20,40,60,80},{"F","D","C","B","A"})

Note Clicking the left or top scroll arrow after the minimum value has been reached or clicking the right or bottom arrow after the maximum value has been reached has no effect on the value returned. The scroll bar remains at either the minimum or maximum value and does not cycle through the range of allowed values.

Top of Page

Add a scroll bar (ActiveX control)

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Scroll Bar .

Click the worksheet location where you want the upper-left corner of the scroll bar to appear.
To edit the control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .
To specify the control properties, on the Developer tab, in the Controls group, click Properties .

Tip You can also right-click the control, and then click Properties.

Summary of properties by functional categories

If you want to specify	Use this property
General:
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)
Whether the control can receive the focus and respond to user-generated events.	Enabled (Form)
Whether the control can be edited.	Locked (Form)
The name of the control.	Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)
Whether the control can be printed.	PrintObject (Excel)
Whether the control is visible or hidden.	Visible (Form)
Data and binding:
The range that is linked to the control's value.	LinkedCell (Excel)
The content or state of the control.	Value (Form)
Size and position:
The height or width in points.	Height, Width (Form)
The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)
Whether the orientation is vertical or horizontal.	Orientation (Form)
Formatting:
The background color.	BackColor (Form)
The foreground color.	ForeColor (Form)
Whether the control has a shadow.	Shadow (Excel)
Keyboard and mouse:
A custom mouse icon.	MouseIcon (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam).	MousePointer (Form)
Specific to scroll bar:
The delay in milliseconds after you click the scroll bar once.	Delay (Form)
The amount of movement that occurs when the user clicks the area between the scroll box and either of the scroll arrows.	LargeChange (Form)
The maximum and minimum allowed values.	Max, Min (Form)
Whether the size of the scroll box is either proportional or fixed to the scrolling region.	ProportionalThumb (Form)
The amount of movement that occurs when the user clicks a scroll arrow in the control.	SmallChange (Form)

Top of Page

Add a spin button (Form control)

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Spin Button .

Click the worksheet location where you want the upper-left corner of the spin button to appear.
On the Developer tab, in the Controls group, click Properties .

Tip You can also right-click the control, and then click Format Control.

To set the control properties, do the following:

In the Current value box, enter the initial value of the spin button within the range of allowed values below. This value must not be:
- Less than the Minimum value, otherwise the Minimum value is used.
- Greater than the Maximum value, otherwise the Maximum value is used.
In the Minimum value box, enter the lowest value that a user can specify by clicking the bottom arrow in the spin button.
In the Maximum value box, enter the highest value that a user can specify by clicking the top arrow in the spin button.
In the Incremental change box, enter the amount that the value increases or decreases when the arrows are clicked.
In the Cell link box, enter a cell reference that contains the current position of the spin button.

The linked cell returns the current position of the spin button.

Use this value in a formula to respond to value of the cell specified in the Cell link box that corresponds to the current position of the spin button. For example, you create a spin button for setting the current age of an employee with the following properties:

Property	Value
Current value	35
Minimum value	21
Maximum value	70
Incremental change	1
Cell link	C1

With these settings, the user can click the spin button to enter an age that falls within a minimum and maximum age range. The median age of the employees is 35, and therefore 35 is a good choice to set as the starting value.

The following formula in cell D1 determines the length of employment that is based on the current age value in the cell that is linked to the spin button and the employee's age at hire date — the value in B1 (retrieved from another data source). The formula then computes a holiday bonus percentage that is based on years of service:

=(C1 - B1)* .01

Note Clicking the top arrow after the minimum value has been reached or clicking the bottom arrow after the maximum value has been reached has no effect on the value returned. The spin button remains at either the minimum or maximum value and does not cycle through the range of allowed values.

Top of Page

Add a spin button (ActiveX control)

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Spin Button .

Click the worksheet location where you want the upper-left corner of the spin button to appear.
To edit the control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .
To specify the control properties, on the Developer tab, in the Controls group, click Properties .

Tip You can also right-click the control, and then click Properties.

Summary of properties by functional categories

If you want to specify	Use this property
General:
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)
Whether the control can receive the focus and respond to user-generated events.	Enabled (Form)
Whether the control can be edited.	Locked (Form)
The name of the control.	Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)
Whether the control can be printed.	PrintObject (Excel)
Whether the control is visible or hidden.	Visible (Form)
Data and Binding:
The range that is linked to the control's value.	LinkedCell (Excel)
The content or state of the control.	Value (Form)
Size and Position:
The height or width in points.	Height, Width (Form)
The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)
Whether the orientation is vertical or horizontal.	Orientation (Form)
Formatting:
The background color.	BackColor (Form)
The foreground color.	ForeColor (Form)
Whether the control has a shadow.	Shadow (Excel)
Keyboard and Mouse:
A custom mouse icon.	MouseIcon (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam).	MousePointer (Form)
Specific to spin button:
The delay in milliseconds after you click the spin button once.	Delay (Form)
The maximum and minimum allowed values.	Max, Min (Form)
The amount of movement that occurs when the user clicks a spin arrow in the control.	SmallChange (Form)

Top of Page

See Also

Excel > Forms

Add an image control to a worksheet

Excel 2007

You use an image control to embed a picture, such as a bitmap (bitmap: A picture made from a series of small dots, much like a piece of graph paper with certain squares filled in to form shapes and lines. When stored as files, bitmaps usually have the extension .bmp.), JPEG (JPEG: A graphics file format (.jpg extension in Microsoft Windows) supported by many Web browsers that was developed for compressing and storing photographic images. It's best used for graphics with many colors, such as scanned photos.), or GIF (GIF: A graphics file format (.gif extension in Windows) used to display indexed-color graphics on the World Wide Web. It supports up to 256 colors and uses lossless compression, meaning that no image data is lost when the file is compressed.) and, for example, to display a company logo or clip art.

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Image .

Click the worksheet location where you want the upper-left corner of the image control to appear.
To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .
To specify the control properties, on the Developer tab, in the Controls group, click Properties .

Tip You can also right-click the control, and then click Properties.

The Properties dialog box appears. To add an image, click the Build button next to the Image property box, and then browse to an image file in the Load Picture dialog box.

For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.) topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available.

Summary of properties by functional categories

If you want to specify	Use this property
General:
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.)	AutoLoad (Excel)
Whether the control can receive the focus and respond to user-generated events.	Enabled (Form)
Whether the control can be edited.	Locked (Form)
The name of the control.	Name (Form)
The way the control is attached to the cells below it (free floating, move but do not size, or move and size).	Placement (Excel)
Whether the control can be printed.	PrintObject (Excel)
Whether the control is visible or hidden.	Visible (Form)
Size and Position:
Whether the size of the control automatically adjusts to display all contents.	AutoSize (Form)
The height or width in points.	Height, Width (Form)
The distance between the control and the left or top edge of the worksheet.	Left, Top (Form)
Formatting:
The background color.	BackColor (Form)
The background style (transparent or opaque).	BackStyle (Form)
The color of the border.	BorderColor (Form)
The type of border (none or single-line).	BorderStyle (Form)
Whether the control has a shadow.	Shadow (Excel)
The visual appearance of the border (flat, raised, sunken, etched, or bump).	SpecialEffect (Form)
Image:
The bitmap to display in the control.	Picture (Form)
The location of a background picture (top left, top right, center, and so on).	PictureAlignment (Form)
How to display the background picture on the control (crop, stretch, or zoom).	PictureSizeMode (Form)
Whether you want to tile multiple copies of the picture in the control.	PictureTiling (Form)
Keyboard and Mouse:
A custom mouse icon.	MouseIcon (Form)
The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, I-beam).	MousePointer (Form)

See Also

Excel > Forms

Add or edit a macro for a control on a worksheet

Excel 2007

For forms and ActiveX controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) , you can do the following:

Perform an action or operation when a user clicks a form control by assigning a macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) to it.
Run Microsoft Visual Basic for Applications (VBA) (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.) code to process any events that occur when a user interacts with an ActiveX control.

For more information about how to create and edit macros, see the Help section, Macros.

What do you want to do?

Add or edit a macro for a form control

Add or edit a macro for an ActiveX control

Add or edit a macro for a form control

Right-click the control, and then click Assign Macro.

The Assign Macros dialog box appears.

To specify the location of an existing macro, select where the macro is located in the Macros in box by doing one of the following:
- To search for the macro in any workbook that is open, select All Open Workbooks.

Note If the macro that you want to assign to the control is in a separate workbook, open that workbook first so that it will be available in the Macros in list box.

To limit the search for the macro to the workbook that contains the current worksheet, select This Workbook.
To limit the search for the macro to a specific workbook, select that workbook from the list of available open workbook names.

Do one of the following:

Assign a macro Do one of the following:

Record a new macro Click Record, and when you finish recording the macro, on the Developer tab, in the Code group, click Stop Recording .
Assign an existing macro Double-click a macro in the list or enter its name in the Macro name box.
Create a new macro Click New and then, in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.), write a new macro.

For more information about how to write macros, see Visual Basic Help (Microsoft Visual Basic Help: To get help for Visual Basic in Excel, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.).

Modify an assigned macro Do one of the following:

Edit the assigned macro Click the name of the macro in the Macro Name box, and then click Edit.
Assign a different existing macro Double-click a macro in the list or enter its name in the Macro name box.

Top of Page

Add or edit a macro for an ActiveX control

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .
Select the control.

For more information, see Select or deselect controls on a worksheet.

on the Developer tab, in the Controls group, click View Code .

Note You can also edit an existing macro by right-clicking the control, and then clicking View Code.

In the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.), write a new macro or change the existing macro.

After you finish writing the macro, click Close and Return to Microsoft Excel on the File menu in the Visual Basic Editor.
After you finish designing the control, on the Developer tab, in the Controls group, turn off Design Mode .

Top of Page

See Also

Excel > Forms

Add or register an ActiveX control

Excel 2007

In addition to the various Form controls, your computer contains many ActiveX controls installed by Microsoft Office Excel and other programs, such as Calendar Control 12.0, and Windows Media Player. When you install new ActiveX controls (ActiveX control: A control, such as a check box or button that offers options to users or runs macros or scripts that automate a task. You can write macros for the control in Microsoft Visual Basic for Applications or scripts in Microsoft Script Editor.), the Setup program for the controls usually registers each control on your computer, which makes it available to use from Excel. If the control doesn't appear in the list, you must register it manually.

Important Not all ActiveX controls can be used directly on worksheets; some can be used only on Microsoft Visual Basic for Applications (VBA) (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.) UserForms. When you work with these controls, Excel displays the message Cannot insert object if you try to add them to a worksheet.

Add an ActiveX control

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click More Controls .

In the More Controls dialog box, select a control from the list of available controls on your computer.
Click the worksheet location where you want the upper-left corner of the ActiveX control to appear.
To edit the control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn onDesign Mode .
To specify the control properties, on the Developer tab, in the Controls group, click Properties .

Tip You can also right-click the control, and then click Properties.

Top of Page

If the Developer tab is not available, display it.

Display the Developer tab

Click the Microsoft Office Button , and then click Excel Options.
In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

Note The Ribbon is a component of the Microsoft Office Fluent user interface.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click More Controls .

Click the worksheet location where you want the upper-left corner of the ActiveX control to appear.
To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .
At the bottom of the More Controls dialog box, click Register Custom.
In the Register Custom Control dialog box, locate the folder that contains the control file (.ocx file name extension) or dynamic link library file (.dll file name extension) for the control that you want to register.
Select the file for your control, and then click Open.

Top of Page

See Also

Excel > Forms

Add, edit, find, and delete rows by using a data form

Excel 2007

When a row of data is very wide and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows.

What do you want to do?

Learn about data forms

Create a data form

Use a data form

Add a new row of data

Find a row by navigating

Find a row by entering search criteria

Change data in a row

Delete a row

Close a data form

Learn about data forms

A data form provides a convenient means to enter or display one complete row of information in a range or table without scrolling horizontally. You may find that using a data form can make data entry easier than moving from column to column when you have more columns of data than can be viewed on the screen. Use a data form when a simple form of text boxes that list the column headings as labels is sufficient and you don't need sophisticated or custom form features, such as a list box