VBA – User Form & Controls

VBA – User Form & Controls

A UserForm is a custom-built dialog box that makes a user data entry more controllable for you and easier for the user. In this chapter, we will design a Simple form and add data into excel.

Creating a Userform in Excel

 

User forms are created in the Project Window of the Visual Basic Editor. You will also find the toolbox that allows you to add controls to the user forms in the Visual Basic Editor.

 

To insert New UserForm Go to “Insert” and select UserForm

UserForm Controls in VBA

 

Labels in VBA

Label stores text which is not editable by user during run time, and is used to describe other controls.

 

Text Boxes in VBA

The text box is the simplest control that requires an entry by the user. The user types something in it and this value can then be used in your VBA procedure.

 

 

 

Command Buttons in VBA

The command buttons are usually placed at the bottom of the form and allow user to complete the transaction for which the form has been created. The captions on the buttons are usually “Reset” , “Submit” , “Cancel” , etc.

 

Option Buttons, Check Boxes and Frames

You do not need to add a label to add check box or the option button because they come with their own label. The main difference between check boxes and option buttons is that you can select only one from the Option Buttons while we can select multiple check boxes from the list.

 

Combo Boxes in VBA

Combo Box is a list of items from which a user can select item from the given list. if required we can enter own text so combo box is a combination of TextBox and ListBox.

 

 

VBA Spin Buttons

Spin Button is used to increment or decrement the value displayed by other controls, without any drag and drop facility.

 

Image Controls in VBA

Image Control is used to display a Picture or Image on the UserForm. We can place any image in the Image control using Image Source Attribute.

 

Example: Adding Basic Controls in VBA

To load list of cities you need to call UserForm_initilize() Event.

You can get the same event by double click on  userform you will get the form code area,  select userform from the left drop-down and Initialize from the right drop-down

Private Sub UserForm_Initialize()

Private Sub UserForm_Initialize()

With ComboBox1

.AddItem “Delhi”

.AddItem “Columbia”

.AddItem “Gurgaon”

.AddItem “Bangalore”

.AddItem “Agra”

End With

End Sub

 

Example: To Create a User form containing a Text Box Combo Box and Option Buttons as shown above-

 

Double click on Submit button to create button click event code as below-

Private Sub CommandButton1_Click()

MsgBox “Welcome ” & TextBox1.Text

MsgBox “Selected City is : ” & ComboBox1.Text

‘Let’s put a condition to check the value for option button

If OptionButton1 = True Then

MsgBox “Male”

Else

MsgBox “Female”

End If

End Sub

 

Example: Working with Image control as shown below-

To load an image in image control dynamically, double click on Load Image button to write the following code-

 

Private Sub CommandButton1_Click()

Image1.Picture = LoadPicture(“D:\Book2.JPG”)

Image1.Height = “130”

Image1.Width = “120”

Image1.SpecialEffect = fmSpecialEffectRaised

Image1.PictureTiling = False

End Sub

 

Enjoy the tutorial? Please spread the word :)

Follow by Email
Facebook
Facebook
Google+2k
Google+
http://hirdeshbhardwaj.com/2017/09/08/vba-2
YouTube41
YouTube