Let’s create your first project with a control: we will place a button on the worksheet, and when clicked, it will display a dialog box with the greeting « Hello, World! ».
So, follow these steps:
- Click the Button control from the ActiveX Controls toolbar, which is accessed by clicking Insert in the Controls group on the Developer tab of the ribbon.
- Draw a Button control of the required size on the worksheet.
NOTE
The first created Button control will automatically display the caption CommandButton1. If you create a second button, it will display CommandButton2, and so on. The text displayed on the button surface is defined by the Caption property. In addition, VBA sets the value of the control’s Name property (i.e., its object name) by default. For the first created button, the Name property is set to CommandButton1, for the second to CommandButton2, etc.
- Select the created button. Click the Properties button on the Developer tab in the Controls group. The Properties window will appear on the screen. In this window, set the Name property to cmdHello instead of the generic CommandButton1. Set the Caption property to Hello.
TIP
Assigning a meaningful Name value to a control makes the code easier to read, since the control’s name helps to recognize its purpose in the project.
- Again select the created button. Click View Code on the Developer tab in the Controls group. As a result, the Visual Basic editor will open, and the code editor will automatically generate the first and last lines of the procedure handling the button’s Click event.
“Hello, World!” Code skeleton
Private Sub cmdHello_Click() End Sub
- In the button’s Click event procedure, add the instruction that will display a dialog box with the greeting.
“Hello, World!”
Private Sub cmdHello_Click() MsgBox "Hello, World!", vbExclamation End Sub
- Click Design Mode on the Developer tab in the Controls group to exit design mode.
The project is ready. Now you can test your created Hello button. Click it, and if a dialog box with the greeting appears, then everything is correct.
Common Properties of Controls
Controls have a large collection of properties that allow you to configure various object parameters, from its position and size to the text or picture displayed in it.
Table. Common Properties of Controls
| Property | Description |
| AutoSize | Specifies whether the control should automatically resize to fit all displayed content |
| BackColor | Sets the background color |
| BackStyle | Sets background transparency. Possible values: fmBackStyleTransparent or fmBackStyleOpaque |
| BottomRightCell, TopLeftCell | Return references to the cells located under the top-left and bottom-right corners of the control |
| Caption | Defines the text string displayed on the control |
| ControlTipText | Returns the tooltip text |
| Enabled | Determines whether the control is available to the user |
| Font | Returns the Font object to set font parameters |
| ForeColor | Sets the font color |
| Height, Width | Set the control’s height and width |
| Left, Top | Coordinates of the top-left corner of the control |
| MouseIcon | Assigns a custom mouse pointer |
| MousePointer | Specifies the type of mouse pointer |
| Name | Assigns the object’s name |
| OldHeight, OldWidth | Return the height and width of the button before resizing |
| OldLeft, OldTop | Return the coordinates of the button’s top-left corner before moving |
| Parent | Specifies a reference to the container object of this control |
| Picture | Sets the reference to a bitmap file used as the control’s background |
| PicturePosition | Sets the position of the picture relative to the caption. Possible values: fmPicturePositionLeftTop, fmPicturePositionLeftCenter, fmPicturePositionLeftBottom, fmPicturePositionRightTop, fmPicturePositionRightCenter, fmPicturePositionRightBottom, fmPicturePositionAboveLeft, fmPicturePositionAboveCenter, fmPicturePositionAboveRight, fmPicturePositionBelowLeft, fmPicturePositionBelowCenter, fmPicturePositionBelowRight, fmPicturePositionCenter |
| PrintObject | Determines whether the control should be printed |
| Tag | Sets a parameter used to identify the specific control |
| TakeFocusOnClick | Determines whether the control receives focus after being clicked |
| Visible | Sets the visibility of the control |
| WordWrap | Specifies whether words should wrap if they don’t fit on one line of the control |
Common Methods of Controls
Controls have several common methods that allow moving, positioning, and managing focus.
Table. Common Methods of Controls
| Method | Description |
| Move | Moves the control |
| SetFocus | Sets focus to the control |
| BringToFront, SendToBack | Place the control in the foreground or background |
| ZOrder | Places the control in front of or behind other controls, depending on the parameter value (fmTop or fmBottom) |
Common Events of Controls
Controls have a wide collection of procedures capable of intercepting and handling various system events and user actions, from mouse clicks to error detection.
Table. Common Events of Controls
| Event | Description |
| BeforeDragOver | Occurs when data is dragged over the control |
| BeforeDropOrPaste | Occurs before data is dropped or pasted |
| Click | Occurs when the user clicks the control |
| DblClick | Occurs when the user double-clicks the control |
| Enter, Exit | Occur when the control gains or loses focus |
| Error | Occurs when the control encounters an error but cannot pass the message |
| KeyDown, KeyUp | Occur when the user presses or releases any key while the control has focus |
| KeyPress | Occurs when the user presses any key except function keys, cursor control keys, or special keys while the control has focus |
| MouseDown, MouseUp | Occur when the user presses or releases any mouse button |
| MouseMove | Occurs when the user moves the mouse pointer over the control |