Plots In Excel Userform

by admin

Nov 07, 2017 I have a UserForm that plots a series of graphs (5) these are selected via a number of command buttons for each graph. By blakius84 in forum Excel Charting. VBA userforms do not have a builtin shape control for drawing lines or circles. You can mimic vertical and horizontal lines by using a label with no caption and a value of 1 for the width or height. I have an example file for using autoshape displayed as images which allows for a more rich drawing experience. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.

UserForm and its Properties

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

Name Property

This property is used to set the name of the control as per theuser’s ease and understanding. The Name Property of a userform can only bespecified at the design time (not in the run time), whereas the Name property for controls can be specified forboth 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 nameat 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 settingthe name in its right column. Though there are certain rules to specify thename 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 prefixcan 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. Thename property is used internally in the VBA programming, whereas the captionproperty the name is displayed to the user on the userform. It can accept anyletter, numbers, spaces, underscores, and symbols. You can edit the caption byclicking on Caption Property in the properties windowand 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 atthe 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 userformand its controls. You can customize the size by manually entering it in theheight, width property in the properties window. For height and width property,the values are set in points. It can’t accept negative values. Hence, theentered values should be greater or equal to zero. The height width can also beresized by using size handles. Select the control, the point will automaticallychange 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 toall the controls including the userform.

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

Syntax for VBA code:

obj.Height = NumValue;

Excel userform examples

obj.Width = NumValue.

Left, Top Properties

The Left Propertydetermines the measurement of the left edge of the control with respect to theUserform container. In contrast, the Top Property determines themeasurement 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 forTop) are set to zero, the control will appear at the left edge or top edge ofthe userform that contains it. The left or top can also be resized by usingsize handles. Select the control, the point will automatically change to anarrow. You can drag or minimize the arrow as per your requirement, and oncedone later, you can release the mouse. This property is applicable to all thecontrols, including the userform.

This property can be specified either at design time (throughproperty 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 anddisplays its content. Value property has different values with respect to variouscontrols. For the controls CheckBox, OptionButton, and ToggleButton, theinteger value of -1 represents True (the control is selected), integer 0 valuemeans False (the control is cleared) and the Null value will be displayed asshaded as it neither represents selection nor the control clear. For ScrollBarand SpinButton, this property will take integer values set in Max and Minproperty. This property applies to the userformand all controls except Frame, Label, and Image.

This property can be specified either at design time (throughproperty 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 specifiedcontrol. It is also known as an acceleratorkey or access key. To use the accelerator key, a single character is pressed incombination with the Alt key. Accelerator property focuses on control andinitiates the Click event. For example, you can use the ‘enter command’ bysetting the accelerator key to ‘E ‘and pressing it along with ALT (ALT + E). Itis not case-sensitive as the same key is pressed for capital and small. This propertyis only applicable to the Label, CheckBox, OptionButton, CommandButton, ToggleButton,and MultiPage controls.

Alignment Property:

Thisproperty is used to set the placement of the caption with respect to thecontrol. 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 isaligned to the right of the control.

Thisproperty is applicable to OptionButton, CheckBox, and ToggleButton.

Excel

AutoSize Property:

Thisparameter specifies whether the control displaying the entire content should beresized 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 elsefor Boolean FALSE (Default option) it will keep the size of the control as itis. Thisproperty is applicable to the TextBox, Label, CommandButton, ComboBox,CheckBox, ToggleButton, OptionButton, Image, and RefEdit.

BackColor Property:

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

BackStyle Property:

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

  • fmBackStyleTransparent: It represents value 0 whereinthe background style is transparent, and everything is visible behind thecontrol on a UserForm.
  • fmBackStyleOpaque (default value): It represents value1 where the background style is opaque, and nothing is visible at the back ofthe control on a UserForm. To implement the BackColour property you must makesure that the BackStyle property’s background style is set to opaquebackground (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 bordercolor for UserForm and its controls. To implement the border colour propertythe BorderStyle propertyshould be set to a non-zero value (fmBorderStyleNone), for any other this propertywill 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 orthe userform. It has two settings:

  • fmBorderStyleNone: It represents value 0 and have noborder
  • fmBorderStyleSingle (default value): It represents value1 and have a single-line border. The BorderStyle specifies its border colors usingthe BorderColor Property. Todefine the border colour over any control or form the BorderStyle propertyshould be set to a value greter or small than 0 (fmBorderStyleNone) . The SpecialEffect propertyis 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 acontrol in the Userform and mentions a cell or field. Any change in thecontrol’s value will update its linked cell, and similarly, a change in linkedcells 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 pointsand hovers over a control. This property is helpful in educating the users bygiving tips or explanations on using the control. The default value is set toan empty string (no text will be displayed). This propertyis applicable to the Userform and all its controls.

Enabled Property:

This parameter is used to check whether a control can be edited througha VBA code and thus can react to customized events. If this parameter is set tothe Boolean value TRUE (default value), the user can interact and edit thecontrols through VBA else for Boolean FALSE, all the controls get disabled, andthey 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 beedited or not. If it is set to a Boolean value TRUE, it signifies that thecontrol is locked and is no more editable else for a Boolean False the controlcan be edited.

Thisproperty 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 incontrols. 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 itscontrols unless you separately mention the font for the specific control. This property is applicable toallcontrols except SpinButton, ScrollBar, and Image.

ForeColor Property:

Excel userform examples

Thisproperty controls the foreground color to be displayed in the text. Itspecifies the text, arrows (only for ScrollBar and SpinButton), and caption’scolor for controls and userform. This property is applicable to UserForm, and itsall controls except the image.

MouseIcon Property:

Vba Excel Userform Example

Plots In Excel Userform

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

View Userform In Excel Sheet

MousePointer Property:

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

Plots In Excel Userform

Open Userform In Excel

  • 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 andsouthwest direction. It is set by value 6.
  • fmMousePointerSizeNS– It represents a two-headed arrow indicating towards the north and southdirection. It is set by 7.
  • fmMousePointerSizeNWSE– It is a two-headed arrow indicating towards northwest and southeast directionand is set by value 8.
  • fmMousePointerSizeWE– It is a two-headed arrow indicating towards west and east direction. It isset 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, andwest direction. It is set by value 15.
  • fmMousePointerCustom– It represents an image by the MouseIcon property. It is setby value 99.

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

PictureProperty:

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

PictureAlignment Property:

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

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

Thisproperty applies to CheckBox, Label, ToggleButton, OptionButton, andCommandButton.

SpecialEffect Property:

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

Plots In Excel Userform Shortcut

  • 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 andis set by value 1.
  • fmSpecialEffectFlat(default) – This setting is used for UserForm and the controls Image and Labeand is set by value 0.
  • fmSpecialEffectRaised– This setting is used for UserForm and the controls Image and Labe and is setby value 1.
  • fmSpecialEffectSunken– This is used for controls TextBox, ComboBox, and ListBox, and is set by value2.
  • 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 propertyapplies to only Label, ComboBox, TextBox, ListBox, CheckBox, ToggleButton, Frame,OptionButton, Image, and RefEdit.

TabStop Property:

Thisproperty determines the selection criteria for control with the tab key. If itis set with a Boolean value TRUE (default value), the control will be act astab stops else for Boolean False; the control will be ignored and its positionin the tab order stays intact.

Thisapplies to all controls except for the Label and Image control.

Visible Property:

Thisproperty specifies the visibility for control in the form. If it is set to the Booleanvalue 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 hiddencontrol only if any condition is satisfied else the control would remainhidden. This property is applicable to all controls and userform.

WordWrap Property:

Excel Userform Examples

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