VBA Tutorial Login  

Free! Excel VBA Online Tutorial


User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active

Article Index


UserForm and Controls - Properties



UserForm acts as a container in which you add multiple ActiveX controls, each of which has a specific use and associated properties. By itself, a UserForm will not be of much use unless ActiveX controls are added to it which are the actual user-interactive objects. ActiveX controls can be used with VBA code or without, either directly on worksheets, while some can be used only on VBA UserForms. Using ActiveX Controls on a Worksheet have been illustrated in detail, in the separate section of "Excel VBA: ActiveX Controls, Form Controls & AutoShapes on a Worksheet".


Properties can be viewed in an alphabetical order or category wise by clicking the "Alphabetic" or "Categorized" options in the Properties Window, after selecting the respective Userform or Control. To set/edit, click on that property and make the change in its right column. For help on a Userform or Control property, in the Properties Window select the right column of that property and press F1.



Design-Time, Run-time and Break-time:


The time when an application is being developed in VBE (Visual Basic Environment) is termed as design-time. This is the time when you create a UserForm, add controls and set their properties. Run-time is the time when your code is being executed or the application is running. During this time the developer interacts with the application just like a user and the code cannot be edited then. Break-time is the time when an Application encounters any processing error that prevents the code from continuing or being executed further. Design-time manipulations are not permanent, while run-time are not. For ex, if you add a CheckBox in your code using the Add Method [Set ctrl = Controls.Add("Forms.CheckBox.1")], it will appear when the UserForm shows but when you return to VBE, the CheckBox will not be present. Similarly, if you set the Caption of an OptionButton in your code, the new set Caption will appear when the UserForm is displayed but will revert to its original in VBE. The ControlTipText property is set during design-time but is visible on the control during run-time only.


UserForm or Control properties can be set either at design-time or at run-time. Typically, a property which is dynamic is set at run-time and the more static ones at design-time. Name property of a UserForm can be set only at design-time and cannot be set at run-time. Though you can add, name and set properties of controls at run-time, these are mostly done at design-time itself, as these usually remain static while running a procedure. You might face some complications in adding controls and setting properties at run-time, hence only occasionally do you need to set properties at run-time to appropriately cope with a situation.



Setting control properties with vba:


If the code is in a procedure in the Code Module of the UserForm, use the vba syntax:   Controlname.Property = Setting/Value

If the code is in a Standard Module or in the Code Module of a different UserForm, use vba syntax:   UserFormName.Controlname.Property = Setting/Value


Examples of VBA Syntax:

Label1.Font.Name = "Arial"; Label1.ForeColor = RGB(255, 255, 0); OptionButton1.BackColor = 255; CheckBox1.Value = False; CheckBox1.Alignment = fmAlignmentLeft; TextBox1.MultiLine = True; TextBox1.WordWrap = True; TextBox1.ScrollBars = 2; OptionButton1.AutoSize = True; Me.TextBox1.Enabled = False; TextBox1.TextAlign = fmTextAlignLeft; TextBox1.Text = "Hello"; CommandButton1.Left = 50; TextBox1.MaxLength = 5;  Note: Label1, TextBox1, CheckBox1, OptionButton1 & CommandButton1 are the Control names.




Bottom Ad

© 2014 GlobaliConnect.com. All rights reserved.