UserForm and its Properties Excel VBA

UserForm and its Properties

Userform has certain properties that can be viewed as category wise (based on appearance, behavior, font) or in an alphabetic manner. The property window is used to set or edit the features as per the user's needs and requirements. To change or modify, click on the property and amend it in its right column.

Name Property

This property is used to set the name of the control as per the user’s ease and understanding. The Name Property of a userform can only be specified at the design time (not in the run time), whereas the Name property for controls can be specified for both at design-time or run-time. Once the control name is set at design time, it can’t be altered at the run time.  But, if the user is introducing the control at design-time, he can’t change the name at run-time. The default name for the controls are "UserForm1", “CheckBox1”, “OptionButton1”, “TextBox1”, and so on.

The name can be changed by clicking on the name property and setting the name in its right column. Though there are certain rules to specify the name which are as follows:

• The name should start with a letter.
• It can have a combination of alphabets, underscores, and numbers.
• You can’t use Spacing or Symbols to define the name
• The name can have a maximum length of 40 characters.
• For easier readability, the characters next to the prefix can begin with a capital letter. 

Caption Property

This property is used to define the UserForm’s or Control’s user visibility name in text format. The name property is used internally in the VBA programming, whereas the caption property the name is displayed to the user on the userform. It can accept any letter, numbers, spaces, underscores, and symbols. You can edit the caption by clicking on Caption Property in the properties window and can amend the caption name in its right column. You can re-write the Caption for Userform, Label, CommandButton & ToggleButton control, Tab, Page, etc,. The Caption property can be specified at the design time (in the Properties window) or at run time (through VBA code). The syntax is as follows:

Syntax for VBA code

obj.Caption = CaptionName.

Height, Width Properties

This property is used to set the height and width of the userform and its controls. You can customize the size by manually entering it in the height, width property in the properties window. For height and width property, the values are set in points. It can’t accept negative values. Hence, the entered values should be greater or equal to zero. The height width can also be resized by using size handles. Select the control, the point will automatically change to an arrow. You can drag or minimize the arrow as per your requirement, and once done later you can release the mouse. This property is applicable to all the controls including the userform.

This property can be specified either at design time (through property window) or at run time (with VBA code).

Syntax for VBA code:  

obj.Height = NumValue;  

obj.Width = NumValue.

Left, Top Properties

The Left Property determines the measurement of the left edge of the control with respect to the Userform container. In contrast, the Top Property determines the measurement of the top edge of the control and with respect to the userform container. The Left, Top values is set in pixels in the property window.  If any of the values (either for left or for Top) are set to zero, the control will appear at the left edge or top edge of the userform that contains it. The left or top can also be resized by using size handles. Select the control, the point will automatically change to an arrow. You can drag or minimize the arrow as per your requirement, and once done later, you can release the mouse. This property is applicable to all the controls, including the userform.

This property can be specified either at design time (through property window) or at run time (with VBA code).

Syntax for VBA code:  

obj.Left = NumValue;  

obj.Top = NumValue.

Value Property

This property evaluates the selection position of control and displays its content. Value property has different values with respect to various controls. For the controls CheckBox, OptionButton, and ToggleButton, the integer value of -1 represents True (the control is selected), integer 0 value means False (the control is cleared) and the Null value will be displayed as shaded as it neither represents selection nor the control clear. For ScrollBar and SpinButton, this property will take integer values set in Max and Min property. This property applies to the userform and all controls except Frame, Label, and Image.

This property can be specified either at design time (through property window) or at run time (with VBA code).

Syntax for VBA code:  

obj.Value = VariantValue.

Accelerator Property:

Accelerator property is used to set the key to access a specified control.  It is also known as an accelerator key or access key. To use the accelerator key, a single character is pressed in combination with the Alt key. Accelerator property focuses on control and initiates the Click event. For example, you can use the ‘enter command’ by setting the accelerator key to ‘E ‘and pressing it along with ALT (ALT + E). It is not case-sensitive as the same key is pressed for capital and small. This property is only applicable to the Label, CheckBox, OptionButton, CommandButton, ToggleButton, and MultiPage controls. 

Alignment Property:

This property is used to set the placement of the caption with respect to the control. Two alignments are available:

• fmAlignmentLeft – It represents value 0 where the caption is aligned to the left of the control.
• (ii) fmAlignmentRight (default value) - It represents value 1 where the caption is aligned to the right of the control.

Thisproperty is applicable to OptionButton, CheckBox, and ToggleButton.

AutoSize Property:

This parameter specifies whether the control displaying the entire content should be resized automatically or not. If this property is set a to Boolean value TRUE, it will automatically resize the given control as per the content’s size else for Boolean FALSE (Default option) it will keep the size of the control as it is. Thisproperty is applicable to the TextBox, Label, CommandButton, ComboBox, CheckBox, ToggleButton, OptionButton, Image, and RefEdit.

BackColor Property:

This property is used to set the background color for controls of the UserForm. To implement the BackColour property you must make sure that the BackStyle property’s background style is set to opaque background (fmBackStyleOpaque). This property is applicable to all controls.

BackStyle Property:

This parameter is used to set the background of Controls with Opaque or Transparent parameters. It can be set for the following background style for an object:

• fmBackStyleTransparent: It represents value 0 wherein the background style is transparent, and everything is visible behind the control on a UserForm.
• fmBackStyleOpaque (default value): It represents value 1 where the background style is opaque, and nothing is visible at the back of the control on a UserForm. To implement the BackColour property you must make sure that the BackStyle property’s background style is set to opaque background (fmBackStyleOpaque).

This property is applicable to the Label, CommandButton, TextBox, ComboBox, CheckBox, ToggleButton, OptionButton, Image, and RefEdit.

BorderColor Property:

As the name specifies, this property is used to set the border color for UserForm and its controls. To implement the border colour property the BorderStyle property should be set to a non-zero value (fmBorderStyleNone), for any other this property will have no effect. This property is applicable to Label, ComboBox, TextBox, ListBox, Image, Frame, and RefEdit.

BorderStyle Property:

This property is used to specify the type of border for control or the userform. It has two settings:

• fmBorderStyleNone: It represents value 0 and have no border
• fmBorderStyleSingle (default value): It represents value 1 and have a single-line border. The BorderStyle specifies its border colors using the BorderColor Property. To define the border colour over any control or form the BorderStyle property should be set to a value greter or small than 0 (fmBorderStyleNone) . The SpecialEffect property is used to specify the border for a control.

This property is applicable to TextBox, ListBox, Label, ComboBox, Image, Frame, and RefEdit.

ControlSource Property:

This property is used to set or store the Value property of a control in the Userform and mentions a cell or field. Any change in the control’s value will update its linked cell, and similarly, a change in linked cells will automatically update its control value.  

This property is applicable to ComboBox, TextBox, ListBox, OptionButton, CheckBox, ToggleButton, SpinButton, and ScrollBar.

ControlTipText Property:

This property is used to display the text whenever the user points and hovers over a control. This property is helpful in educating the users by giving tips or explanations on using the control. The default value is set to an empty string (no text will be displayed). This property is applicable to the Userform and all its controls.

Enabled Property:

This parameter is used to check whether a control can be edited through a VBA code and thus can react to customized events. If this parameter is set to the Boolean value TRUE (default value), the user can interact and edit the controls through VBA else for Boolean FALSE, all the controls get disabled, and they can’t be accessed through VBA.

This property is applicable to all controls and UserForm.

Locked Property:

This parameter is used to check whether the control could be edited or not. If it is set to a Boolean value TRUE, it signifies that the control is locked and is no more editable else for a Boolean False the control can be edited.

This property applies to the controls TextBox, ListBox, ComboBox, CheckBox, OptionButton, CommandButton, ToggleButton, and RefEdit.

Font Object:

This property defines the kind of font used in userform or in controls. You can easily play around with the font by specifying its font name, font style, font size. The fonts set for a UserForm is applicable to all its controls unless you separately mention the font for the specific control. This property is applicable to all controls except SpinButton, ScrollBar, and Image.

ForeColor Property:

This property controls the foreground color to be displayed in the text. It specifies the text, arrows (only for ScrollBar and SpinButton), and caption’s color for controls and userform. This property is applicable to UserForm, and its all controls except the image.

MouseIcon Property:

This property sets an image to control, which gets displayed whenever the mouse pointer is mover across the control. The assigned image is specified by the actual path and filename for the selected file. This property can only be used if the MousePointer property is set to value 99 (fmMousePointerCustom). This property applies to UserForm and all its controls, except the MultiPage property.

MousePointer Property:

This property determines the sort of mouse pointer that would be displayed when the user moves the mouse over a control. There are 15 settings:

• fmMousePointerDefault (default value) – It is the standard default pointer set by value 0 
• fmMousePointerArrow – It represents an arrow and is set by value 1.
• fmMousePointerCross – It is a cross-hair pointer and is set by 2.
• fmMousePointerIBeam – It represents I-Beam set by value 3.
• fmMousePointerSizeNESW – It represents a two-headed arrow indicating towards the northeast and southwest direction. It is set by value 6.  
• fmMousePointerSizeNS – It represents a two-headed arrow indicating towards the north and south direction. It is set by 7.
• fmMousePointerSizeNWSE – It is a two-headed arrow indicating towards northwest and southeast direction and is set by value 8.
• fmMousePointerSizeWE – It is a two-headed arrow indicating towards west and east direction. It is set by value 9.
• fmMousePointerUpArrow (value 10) – It represents an up arrow and is set by value 10.
• fmMousePointerHourglass – It represents hourglass and is set by value 11.
• fmMousePointerNoDrop – It represents a circle with a diagonal line and is set by value 12.
• fmMousePointerAppStarting – It represents an arrow and hourglass. It will set by value 13.
• fmMousePointerHelp – It represents an arrow and question mark and is set by value 14.
• fmMousePointerSizeAll – It represents a four-headed arrow, indicating towards north, south, east, and west direction. It is set by value 15.
• fmMousePointerCustom – It represents an image by the MouseIcon property. It is set by value 99.

This property applies to UserForm and all its controls, except for the MultiPage property.

Picture Property:

The picture property determines the picture to be displayed on the control where the picture is assigned by specifying the path and filename of the file it is in. To remove any picture, press the DELETE button instead of pressing the BACKSPACE button (it will not remove). This property applies CheckBox, Label, OptionButton, ToggleButton, CommandButton, MultiPage, Frame, and Image.

PictureAlignment Property:

This property is used to determine the alignment of the picture with its caption. This property will be disabled and have no effect if no picture to be displayed is not specified. There are few settings for this property which specifies the position of the caption with respect to the image:

1. fmPictureAlignmentTopLeft (value 0)
2. fmPictureAlignmentTopRight (value 1)
3. fmPictureAlignmentCenter (value 2)
4. fmPictureAlignmentBottomLeft (value 3) 
5. fmPictureAlignmentBottomRight (value 4) 

This property applies to CheckBox, Label, ToggleButton, OptionButton, and CommandButton.

SpecialEffect Property:

This property defines visual appearance (Flat, Raised, Hollow, Bump, Etched) of a control. This property sets the border colors by using system colors. The SpecialEffect property can’t be used with Borderstyle property. If a non-zero value is set for either one of the properties, the other would be automatically set to zero. It has the following settings:

• fmButtonEffectFlat; It is used for optionbutton, checkbox, and togglebutton and is set by value 0.
• fmButtonEffectSunken (Default value) - It is used for optionbutton, checkbox, and togglebutton and is set by value 1.
• fmSpecialEffectFlat (default) – This setting is used for UserForm and the controls Image and Labe and is set by value 0.
• fmSpecialEffectRaised - This setting is used for UserForm and the controls Image and Labe and is set by value 1.
• fmSpecialEffectSunken – This is used for controls TextBox, ComboBox, and ListBox, and is set by value 2.
• fmSpecialEffectEtched (default value) – This is used for Frame and is set to value 3.
• fmSpecialEffectBump - This is used for Frame and is set to value 6.

This property applies to only Label, ComboBox, TextBox, ListBox, CheckBox, ToggleButton, Frame, OptionButton, Image, and RefEdit.

TabStop Property:

This property determines the selection criteria for control with the tab key. If it is set with a Boolean value TRUE (default value), the control will be act as tab stops else for Boolean False; the control will be ignored and its position in the tab order stays intact.

This applies to all controls except for the Label and Image control.

Visible Property:

This property specifies the visibility for control in the form. If it is set to the Boolean value TRUE (default value), and the control is visible else for Boolean False, the control will be hidden. This property can be used to display a hidden control only if any condition is satisfied else the control would remain hidden. This property is applicable to all controls and userform.

WordWrap Property:

This property wraps the text of the control to the next line. WordWrap property will perform the action if it is set to Boolean TRUE, else for Boolean FALSE, it is disabled. Also, if the UserForm’s MultiLine property is set to False, WordWrap will be automatically disabled. This property applies to the controls TextBox, Label, CheckBox, ToggleButton, CommandButton, OptionButton, and RefEdit.