Saturday, July 3, 2021

Add a label or text box to a worksheet

If someone's entering data inaccurately, or you think a coworker may be confused about how to enter data, add a label. A simple name, such as "Phone," lets others know what to put in a cell, and your labels can also provide more complex instructions.

You can add labels to forms and ActiveX controls.

Add a label (Form control)

  1. Click Developer, click Insert, and then click Label Button image .

    Controls group

  2. Click the worksheet location where you want the upper-left corner of the label to appear.

  3. To specify the control properties, right-click the control, and then click Format Control.

  1. Click Developer and then click Insert, and under ActiveX Controls, click Label Button image .

    Controls group

  2. Click the worksheet location where you want the upper-left corner of the label to appear.

  3. Click Design Mode Button image .

  4. Click the worksheet location where you want the upper-left corner of the label to appear.

  5. To specify the control properties, click Properties Button image .

    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 topic. You can also type the property name in the Visual Basic Help Search box. This table summarizes the properties.

    Summary of label properties by functional category

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)

  1. Click Developer and then click Insert, and under ActiveX Controls, click Text Box Button image .

    Controls group

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

  3. To edit the ActiveX control, click Design Mode Button image .

  4. To specify the control properties, click Properties Button image .

    Tip: You can also right-click the text box, 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 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 text box properties by functional category

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)

  1. Click File, click Options, and then click Customize Ribbon.

  2. Under Main Tabs , select the Developer check box, and then click OK.

A label identifies the purpose of a cell or text box, displays brief instructions, or provides a title or caption. A label can also 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 in a cell. A text box can also be a static, and display data users can only read. 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 on a form control:

Example of a Form label control

An ActiveX control label:

Example of an ActiveX label control

An ActiveX text box control:

Example of an ActiveX text box control

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. Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols, such as Y6dh!et5, not House27. Passwords should be 8 or more characters;14 is better.

    And don't forget your password. If you do, we can't help you retrieve it. Office doesn't have a master key to unlock anything. Store passwords in a secure place away from the information 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.

No comments:

Post a Comment