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.
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.
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
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 Propertieson 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 or spin button.
Note You cannot print a data form. In addition, because a data form is a modal dialog box, you cannot use either the Excel Print command or Print button until you close the data form. Alternatively, you can use the Windows Print Screen key to make an image of the form, and then copy it to Microsoft Paint or another program. For more information, see the help topic, Copy the active window or screen contents.
All items that begin with the comparison criteria are filtered. For example, if you type the text Dav as a criterion, Excel finds "Davidovski" and "Davis." To find text values that share some characters but not others, use a wildcard character as your criterion.
The following wildcard characters can be used as comparison criteria for filters, and when you search for and replace content.
Use
To find
? (question mark)
Any single character For example, sm?th finds "smith" and "smyth"
* (asterisk)
Any number of characters For example, *east finds "Northeast" and "Southeast"
~ (tilde) followed by ?, *, or ~
A question mark, asterisk, or tilde For example, fy91~? finds "fy91?"
To find rows that match the criteria, click Find Next or Find Prev.
To return to the data form so that you can add, change, or delete rows, click Form.
You can edit the text that is displayed in a Form control or ActiveX control, such as a label or help information, and you can edit the alternative text description for a control.
Edit an alternative text description for a control
Web browsers display alternative text (alt text) while images are loading, if pictures are missing, and can also display alternative text if you position the pointer over an image. Web search engines use alternative text to help find Web pages. Alternative text is also used to help users who have disabilities.
Note You cannot combine Form controls or ActiveX controls with Drawing Tools or SmartArt Tools objects (such as Shapes and SmartArt graphics) in the same group selection.
Do one of the following:
Group controls
If one or more controls is an ActiveX control, do the following:
You can copy one or more controls in several ways.
Note You cannot combine Form controls or ActiveX controls with Drawing Tools or SmartArt Tools objects (such as Shapes and SmartArt graphics) in the same group selection.
By using the Copy and Paste commands
If one or more controls is an ActiveX control, do the following:
You can move one or more controls in several ways.
Note You cannot combine Form controls and ActiveX controls with Drawing Tools or SmartArt Tools objects (such as Shapes and SmartArt graphics) in the same group selection.
By using the mouse
If one or more controls is an ActiveX control, do the following:
Position the pointer over the control until the pointer changes to a cross pointer, and then drag the selected control or controls to another location.
By using the keyboard
If one or more controls is an ActiveX control, do the following:
Note Form and ActiveX controls have an object stacking order that is separate and distinct from the Drawing Tools and SmartArt Tools objects, such as Shapes and SmartArt graphics.
If one or more controls is an ActiveX control, do the following:
Click the arrow next to the Quick Access Toolbar, and then click More Commands.
Under Choose commands from, select All Commands.
In the list, select Align.
Click Add, and then click OK.
Note You cannot combine Form controls and ActiveX controls with SmartArt Tools objects (such as Shapes and SmartArt) in the same group selection.
Align all controls to the grid or to shapes
Do one of the following:
To align all objects with the upper-left corner of a cell, on the Quick Access Toolbar, click the arrow next to Align, and then click Snap to Grid.
Note The Snap To feature works whether gridlines are turned on or off. You can also align controls with cell gridlines by holding the ALT key while you move a control or object.
To automatically align controls with the vertical and horizontal edges of other shapes when you move or draw them, on the Quick Access Toolbar, click the arrow next to Align, and then click Snap to Shape.
Align controls by their edges
Important Aligning controls can cause them to stack on top of each other. Make sure that the controls are positioned relative to each other in the way that you want before you run a command to align them.
If one or more controls is an ActiveX control, do the following:
Yes, it is true. You can create great forms with little or no need for Microsoft Visual Basic for Applications (VBA) code in Microsoft Office Excel. By using forms and the many controls and objects that you can add to them, you can significantly enhance data entry on your worksheets and improve the way your worksheets are displayed.
A form, whether printed or online, is a document designed with a standard structure and format that makes it easier to capture, organize, and edit information.
Printed forms contain instructions, formatting, labels, and blank spaces for writing or typing data. You can use Excel and Excel templates to create printed forms.
For more information about Excel templates, see the See Also section.
There are several types of forms that you can create in Excel: data forms, worksheets that contain Form and ActiveX controls, and VBA UserForms. You can use each type of form by itself, or you can combine them in different ways to create a solution that's right for you.
Data form
A data form (data form: A dialog box that displays one complete record at a time. You can use data forms to add, change, locate, and delete records.) provides a convenient way 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 or spin button.
A worksheet is a type of form that enables you to enter and view data on the grid, and there are several control-like features already built-in to Excel worksheets, such as comments and data validation. Cells resemble text boxes in that you can enter and format them in a variety of ways. Cells are often used as labels, and by adjusting cell height and width and merging cells, you can make a worksheet behave like a simple data entry form. Other control-like features, such as cell comments, hyperlinks, background images, data validation, conditional formatting, embedded charts, and AutoFilter can make a worksheet behave like an advanced form.
Because controls and objects are stored on the drawing canvas, you can display or view controls and objects alongside associated text that is independent of row and column boundaries without changing the layout of a grid or table of data on your worksheet. Most of the time, many of these controls can also be linked to cells on the worksheet and do not require VBA code to make them work. You can set properties that determine whether a control floats freely or moves and resizes together with a cell. For example, you might have a check box that you want to move together with its underlying cell when the range is sorted. However, if you have a list box that you want to keep in a specific location at all times, you probably do not want it to move together with its underlying cell.
Form controls are the original controls that are compatible with earlier versions of Excel, starting with Excel version 5.0. Form controls are also designed for use on XLM macro sheets.
You can also run macros by using Form controls. You can attach an existing macro to a control, or write or record a new macro. When a user of the form clicks the control, the control runs the macro.
However, these controls cannot be added to UserForms, used to control events, or modified to run Web scripts on Web pages.
Summary of form controls
Button Name
Example
Description
Label
Identifies the purpose of a cell or text box, or displays descriptive text (such as titles, captions, pictures) or brief instructions.
Group box
Groups related controls into one visual unit in a rectangle with an optional label. Typically, option buttons, check boxes, or closely related contents are grouped.
Button
Runs a macro that performs an action when a user clicks it. A button is also referred to as a push button.
Check box
Turns on or off a value that indicates an opposite and unambiguous choice. You can select more than one check box on a worksheet or in a group box. A check box can have one of three states: selected (turned on), cleared (turned off), and mixed, meaning a combination of on and off states (as in a multiple selection).
Option button
Allows a single choice within a limited set of mutually exclusive choices; an option button is usually contained in a group box or a frame. An option button can have one of three states: selected (turned on), cleared (turned off), and mixed, meaning a combination of on and off states (as in a multiple selection). An option button is also referred to as a radio button.
List box
Displays a list of one or more items of text from which a user can choose. Use a list box for displaying large numbers of choices that vary in number or content. There are three types of list boxes:
A single-selection list box enables only one choice. In this case, a list box resembles a group of option buttons, except that a list box can handle a large number of items more efficiently.
A multiple-selection list box enables either one choice or contiguous (adjacent) choices.
An extended-selection list box enables one choice, contiguous choices, and noncontiguous (or disjointed) choices.
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 requires the user to click the down arrow to display the list of items. Use a combo box to enable a user to either type an entry or choose only one item from the list. The control displays the current value in the text box, regardless of how that value is entered.
Scroll bar
Scrolls through a range of values when you click the scroll arrows or drag the scroll box. In addition, you can move through a page (a preset interval) of values by clicking the area between the scroll box and either of the scroll arrows. Typically, a user can also type a text value directly into an associated cell or text box.
Spin button
Increases or decreases 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. Typically, a user can also type a text value directly into an associated cell or text box.
Note The following controls are unavailable in Office Excel 2007 workbooks. These controls can be used in Excel version 5.0 dialog sheets only.
You can also control different events that occur when an ActiveX control is interacted with. For example, you can perform different actions, depending on which choice a user selects from a list box control, or you can query a database to refill a combo box with items when a user clicks a button. You can also write macros that respond to events associated with ActiveX controls. When a user of the form interacts with the control, your VBA code then runs to process any events that occur for that control.
Your computer also contains many ActiveX controls that were installed by Excel and other programs, such as Calendar Control 12.0 and Windows Media Player.
However, ActiveX controls cannot be added to chart sheets from the user interface or to XLM macro sheets. You also cannot assign a macro to run directly from an ActiveX control the same way you can from a Form control.
Summary of ActiveX controls
Button Name
Example
Description
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. A check box can have one of three states: selected (turned on), cleared (turned off), and mixed, meaning a combination of on and off states (as in a multiple selection).
Text box
Enables you to, in a rectangular box, view, type, 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.
Command button
Runs a macro that performs an action when a user clicks it. A command button is also referred to as a push button.
Option button
Allows a single choice within a limited set of mutually exclusive choices usually contained in a group box or frame. An option button can have one of three states: selected (turned on), cleared (turned off), and mixed, meaning a combination of on and off states (as in a multiple selection). An option button is also referred to as a radio button.
List box
Displays a list of one or more items of text from which a user can choose. Use a list box for displaying large numbers of choices that vary in number or content. There are three types of list boxes:
A single-selection list box enables only one choice. In this case, a list box resembles a group of option buttons, except that a list box can handle a large number of items more efficiently.
A multiple selection list box enables either one choice or contiguous (adjacent) choices.
An extended-selection list box enables one choice, contiguous choices, and noncontiguous (or disjointed) choices.
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 requires the user to click the down arrow to display the list of items. Use to allow a user to either type an entry or choose only one item from the list. The control displays the current value in the text box, regardless of how that value is entered.
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.
Spin button
Increases or decreases 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. Typically, a user can also type a text value into an associated cell or text box.
Scroll bar
Scrolls through a range of values when you click the scroll arrows or drag the scroll box. In addition, you can move through a page (a preset interval) of values by clicking the area between the scroll box and either of the scroll arrows. Typically, a user can also type a text value directly into an associated cell or text box.
Label
Identifies the purpose of a cell or text box, displays descriptive text (such as titles, captions, pictures), or provides brief instructions.
A rectangular object with an optional label that groups related controls into one visual unit. Typically, option buttons, check boxes, or closely related contents are grouped in a frame control.
Note The ActiveX frame control is not available in the ActiveX Controls section of the Insert command. However, you can add the control from the More Controls dialog box by selecting Microsoft Forms 2.0 Frame.
More Controls
Displays a list of additional ActiveX controls available on your computer that you can add to a custom form, such as Calendar Control 12.0 and Windows Media Player. You can also register a custom control in this dialog box.
You may also want to include SmartArt graphics, Shapes, WordArt, and text boxes on your form. You can resize, rotate, flip, color, and combine these objects to create even more complex shapes. When you type text directly in a Shape or text box object, the text becomes part of that object — if you rotate or flip the object, the text rotates or flips with it. Unlike ActiveX controls, you can assign different attributes, such as font size and font style, to individual words and characters in the object. You can also assign macros and add hyperlinks to these objects. You can even link text in a Shape or text box object to a worksheet cell and dynamically display updated values in those objects.
Working with controls and objects on the worksheet form
After adding forms and ActiveX to a worksheet form, you usually want to fine-tune and rearrange the controls in a variety of ways to create a well-designed, user friendly form. Common tasks include the following:
Controlling the display of gridlines while you work with the controls, and deciding whether to display the gridlines to the user on the final worksheet form.
Selecting and deselecting controls so that you can specify properties or make additional adjustments.
Editing text in a control, such as the caption or label.
Grouping, copying, moving, and aligning controls to organize the layout of the worksheet form.
Resizing and formatting controls to obtain the appearance that you want.
Positioning or sizing a control with a cell.
Protecting controls and linked cells according to your specific data protection needs.
Enabling or disabling the printing of controls when the worksheet form is printed.
Deleting unused controls.
You can design a worksheet form with or without cell gridlines in the background. For example, you might want to turn off cell gridlines and then format all the cells with the same color or pattern, or even use a picture as a sheet background. To hide or show the gridlines, on the View tab, in the Show/Hide group, clear or select the Gridlines check box.
For more information, see the See Also section of this topic.
Determining the type of control that is on your worksheet
Because there are three different types of controls and objects that you can modify uniquely, you might not know for sure which type of control it is just by looking at it. To determine the type of control (Form or ActiveX), select and right-click the control, and then display the shortcut menu:
If the shortcut menu contains the command Properties, the control is an ActiveX control, and you are in design mode.
If the shortcut menu contains the command Assign Macro, the control is a Form control.
Tip To display the correct shortcut menu for the group box Form control, make sure that you select the perimeter instead of the interior of the group box.
If the shortcut menu contains the command Edit Text, the object is a Drawing object.
Insert a UserForm into your workbook's VBAProject. You access a workbook's VBAProject by first displaying the Visual Basic Editor (press ALT+F11) and then, on the Insert menu, clicking UserForm.
Write a procedure to display the UserForm.
Add ActiveX controls.
Modify properties for the ActiveX controls.
Write event-handler procedures for the ActiveX controls.
By using UserForms, you can also utilize advanced form functionality,. For example, you can programmatically add a separate option button for each letter of the alphabet or you can add a check box for each item in a large list of dates and numbers.
Before creating a UserForm, consider using built-in dialog boxes available from Excel that might fit your needs. These built-in dialog boxes include the VBA InputBox and MsgBox functions, the Excel InputBox method, GetOpenFilename method, GetSaveAsFilename method, and the Dialogs object of the Application object, which contains all the built-in Excel dialog boxes.
At times, you may want a control to act independently of its underlying cell (the cell which contains the upper leftmost corner of the control). At other times, you may want the control to move and size with its underlying cell. For example, if you have a check box that is linked with a particular cell or row, you may want that check box to move with the row when the range is sorted. However, if you have a list box that you want to keep in a specific location at all times, you do not want it to move with its underlying cell.
Right-click the selection, click Format Control, and then click the Properties tab.
Under Object Positioning, do one of the following:
To make the control stay with a cell when the cell moves (for example, when it is sorted) and to resize the control when the cell height and width changes, select Move and size with cells.
Note This option is not available for Form controls.
To make the control stay with a cell when the cell moves (for example, when the range or table that contains the cell is sorted), but keep the control's size independent of the changes to the cell's height and width, select Move but don't size with cells.
To keep the control's position and size independent of the changes to the cell's position, height, and width, select Don't move or size with cells.
Notes
The underlying cell is determined by the location of the upper leftmost corner of the control.
Make sure that Cut, copy, and sort inserted objects with their parent cells option is set in the Excel Options dialog box. Click the Microsoft Office Button, click Excel Options and then, in the Advanced category, under Cut, copy, and paste, select the Cut, copy, and sort inserted objects with their parent cells check box.
If you want controls to be sorted and filtered with their underlying cells, for best results, use ActiveX controls and fit the control to the exact height and width of the underlying cell. (Press ALT when you move and size the cell.)
A control cannot be moved with its underlying cell if the control is locked and if the worksheet on which it is located is protected. For more information, see the See Also section.
To prevent a user from moving, sizing, or deleting a control, right-click the selection, and then click Format Control.
On the Protection tab, select the Locked check box.
If the control has a linked cell, unlock the cell so that the control can write to it, and then hide the cell so that a user cannot cause unexpected problems by modifying the current value. To unlock the cell:
On the Home tab, in the Cells group, click Format, and then click Format Control.
On the Protection tab, clear the Locked check box, and then click OK.
Then, hide the column or row that contains the linked cell, or move the contents of the linked cell to a separate worksheet, and then hide that worksheet.
To create a professional looking appearance for a worksheet form, you should provide a consistent format to all controls and objects, and size them appropriately.
To increase or decrease the size in one or more directions, drag the mouse away from or toward the center.
To keep the center of a control in the same place, hold down CTRL while you drag the mouse.
To maintain the control's proportions, hold down SHIFT while you drag the mouse.
To maintain the proportions while keeping the center in the same place, hold down CTRL and SHIFT while you drag the mouse.
Release the mouse, and then release CTRL or SHIFT.
Resize by entering measurements
Right-click the selection, and then click Format Control.
On the Size tab, enter measurements for the height and width of the control, or click the up or down arrow keys to move the height and width.
To maintain the control's proportions, select the Lock aspect ratio check box.
Note The Reset button (which resets the original height and width), the Rotation text box, and the Relative to original picture size check box are not available.
Right-click the selection, and then click Format Control.
On the Font tab, select the font type, font style, font size, other formatting options for the selected text.
For the Size value, you can type any number between 1 and 1638. The sizes available in the Size list depend on the selected font and the active printer. Select the Normal Font check box to reset the font, font style, size, and effects to the Normal (default) style.
Click OK.
Right-click the selection, and then click Format Control.
On the Alignment tab, select the text alignment, direction, and orientation options that you want, and then click OK.
Right-click the selection, and then click Format Control.
On the Margins tab, select the internal margin options that you want, and then click OK.
Format a Form control so that it has a 3-D appearance
Note Because the Form label control cannot be formatted to appear with 3-D shading, and the Form button control can appear only with 3-D shading, the 3-D shading check box is not available for either control in the Format Control dialog box.
To work with controls on a worksheet form — for example, to group, copy, resize, or format them — you must first select the controls or cancel the selection. There are several ways to do both.
Note You cannot combine Form controls or ActiveX controls with Drawing Tools or SmartArt Tools objects (such as Shapes and SmartArt graphics) in the same group selection.
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.
Make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode.
On the Home tab, in the Editing group, click Find & Select.
Do one of the following:
To select objects that are hidden, stacked, or behind text, click Select Objects, and then draw a box over the objects.
To open a task pane where you can select, multiselect, show, hide, or change the order of objects, click Selection Pane, and then click the options that you want.
Why are form-related commands or controls on the ribbon disabled?
Symptoms
You try to add a Form control or an ActiveX control to your worksheet, but the commands in the Controls group on the Developer tab or a specific control command button that should be available from the Insert command are disabled.
Cause and Resolution
There are several reasons for this:
The following controls are unavailable in Excel workbooks, and therefore always appear dimmed. These controls are only used with Excel version 5.0 dialog sheets:
Button
Name
Text Field
Combo List - Edit
Combo Drop - Down Edit
Run Dialog
Excel might currently be in data entry mode for a cell, which makes the Insert command unavailable. You might have unintentionally entered data entry mode for a cell that is obscured by a control, or the gridlines are hidden and you cannot easily see that the cell is in data entry mode.
To resolve this, press ESC to exit cell data entry mode.
You might have set Disable all controls without notification in the ActiveX settings for all applications section of the Trust Center.
To resolve this, use a less-restrictive setting.
You might be trying to add an ActiveX control in a document that was signed by an untrusted publisher.
The Properties command is never enabled for the Label, Button, and Group boxform controls.
The Propertiesand View Codecommands are disabled if Excel is not in Design mode and you have activated the ActiveX control.
Selecting a control is sometimes difficult and unpredictable.
Cause
There can be several reasons for this:
Because a Form control can contain text or other objects (such as an image), when you try to select the control, you might not be selecting the entire control if you do not position the pointer precisely on the control border.
Because Excel is not in Design mode, when you try to select an ActiveX control, you might unintentionally start an action, such as running a macro or selecting a check box.
Many controls are positioned close to each other.
Resolution
First, determine the type of control that you want to select. Because there are three different types of controls and objects that you can modify uniquely, you may not know for sure which type of control it is just by looking at it. To determine the type of control you are working with, right-click the control, and then note the following:
If the shortcut menu contains the command Properties, the control is an ActiveX control, and Excel is in Design mode.
If the shortcut menu contains the command Assign Macro, the control is a Form control.
Tip To display the correct shortcut menu for a group box Form control, make sure that you select the perimeter of the group box, and not the interior of the group box.
If the shortcut menu contains the command Edit Text, the object is a Drawing Tools object.
Then, do the following:
For a Form control, do the following:
Make sure that the control border, which has a dotted pattern, is displayed:
Instead of the text border, which has a hatched pattern:
.
Use the following procedure to select the entire control:
Right-click the control, but do not click a command on the shortcut menu.
Press ESC.
For an ActiveX control:
If the Developer tab is not available, display it.
