Creative Blog

Étiquette : useform

  • Properties, Methods, and Events of Custom Forms or UserForms, Excel VBA

    UserForm Properties

    The form has a wide range of properties that allow you to control both its appearance and operational settings. Of course, the most commonly used form properties are those that specify the name of the form and the text displayed in the title bar. The following table lists the main properties of the form.

    Table 1: UserForm Properties

    Property Description
    Name Name of the UserForm
    ActiveControl Returns a reference to the control that has received the focus
    BackColor Background color
    BorderColor Border color
    BorderStyle Border style. Valid values are: fmBorderStyleNone and fmBorderStyleSingle
    CanPaste Defines whether an object can be pasted from the clipboard
    CanRedo Defines whether a redo operation is possible
    CanUndo Defines whether an undo operation is possible
    Caption Form caption (title)
    Cycle Specifies the behavior of elements in Frame and Page containers when focus is lost
    DrawBuffer Defines the memory size used when redrawing an image
    Enabled Determines whether the form is available to the user
    ForeColor Specifies the foreground color
    Height, Width Form height and width
    HelpContextID Link to a chapter in the help file
    InsideHeight, InsideWidth Height and width of the custom part of the form (excluding title bar and border thickness)
    KeepScrollBarsVisible Shows scrollbars. Valid values are: fmScrollBarsNone, fmScrollBarsHorizontal, fmScrollBarsVertical, fmScrollBarsBoth
    Left, Top Coordinates of the top-left corner of the form
    MouseIcon Assigns a custom mouse pointer
    MousePointer Specifies the type of mouse pointer
    Picture Specifies a link to a bitmap file used as a background
    PictureAlignment Specifies the alignment of a bitmap used as a background
    PictureSizeMode Determines whether the image should be scaled
    ScrollHeight, ScrollWidth Sets the height and width of the scrollable area
    ScrollLeft, ScrollTop Sets the top-left coordinate of the scrollable area
    SpecialEffect Sets the appearance of the form
    StartUpPosition Specifies the starting position of the form
    Tag Specifies a value used to identify a specific form
    VerticalScrollbarSide Determines which side of the form the scrollbars appear on
    Visible Sets the visibility of the form
    WhatsThisButton Specifies whether to show a Help button (?)
    Zoom Specifies the zoom level of the displayable element

    UserForm Methods

    A form has many methods that allow you to perform a wide range of operations.

    Table 2: UserForm Methods

    Method Description
    Copy Copies the contents of the object to the clipboard
    Cut Copies and removes the object content to the clipboard
    Hide Hides the form without removing it from memory
    Load Loads an object into memory without displaying it
    Move Moves the form
    Paste Pastes the clipboard contents
    PrintForm Prints an image of the form
    RedoAction Repeats the last redo command
    Repaint Refreshes the image of the form
    Scroll Scrolls the image
    SetDefaultTabOrder Sets the default tab order for tab key navigation
    Show Displays the form
    UndoAction Repeats the last undo command
    Unload Removes an object from memory
    WhatsThisMode Displays a Help pointer with a question mark

    UserForm Events

    Events allow you to create programs that manage the entire lifecycle of a form, from initialization to closing.

    Table 3: UserForm Events

    Event Description
    Activate, Deactivate Occurs when the form is activated and deactivated
    AddControl Occurs when a control is added
    BeforeDragOver Occurs during data drag
    BeforeDropOrPaste Occurs before dragged data is inserted
    Click Occurs when the user clicks the form
    DblClick Occurs when the user double-clicks the form
    Error Occurs when the form encounters an error but cannot send a message
    Initialize Occurs when the form is initialized
    Layout Occurs when the layout of the form changes
    KeyDown, KeyUp Occurs when the user presses or releases a key while the form has focus
    KeyPress Occurs when the user presses a key other than function, arrow, or service keys, while the form has focus
    MouseDown, MouseUp Occurs when the user presses or releases a mouse button
    MouseMove Occurs when the user moves the mouse pointer over the form
    QueryClose Occurs before the form window is closed
    RemoveControl Occurs when a control is removed
    Resize Occurs when the form is resized
    Scroll Occurs during scrolling
    Terminate Occurs when the form is closed
    Zoom Occurs when the zoom level of the form changes

  • Creating a UserForm or Custom Form, Excel VBA

    The first step in creating a UserForm is to insert one into the Visual Basic Editor. To do this, press Alt + F11 to access the VBE and select your workbook’s name in the Project Explorer, as shown in the following figure.

    Accessing the VBE Environment

    With the workbook name selected, click on Insert / UserForm from the menu bar, as illustrated in the next figure.

    Inserting a UserForm

    A new UserForm opens in its design window, as shown in the following figure.

    A New UserForm Opens

  • Macro to Highlight the Contents of Text Fields in a UserForm, Excel VBA

    Use the following macro to highlight the saved content of a text field. For this task, you need a UserForm with a text box and a button. In the development environment, double-click the button and insert the following code:

    Private Sub CommandButton1_Click()
    TextBox1.SetFocus
    TextBox1.SelStart = 0
    TextBox1.SelLength = Len(TextBox1.Text)
End Sub

    Use the SetFocus method to place the mouse pointer on the text field. With the SelStart property, you determine the starting position of the text to be highlighted. The SelLength property defines the number of characters to highlight. Assign the Len function to this property, which determines the total number of characters entered in the text field.