Forms and Controls in Excel VBA

Home/Excel VBA/Forms and Controls in Excel VBA

Any Windows Application is equipped with set of objects called windows controls. Forms and Controls in Excel VBA topics give you the complete understanding of developing application with Forms and Controls.
In This Section:

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


What Are UserForms?

Any Windows Application is equipped with set of objects called windows controls. The Main control is called a Form, it is the primary window contains different types of controls which allow user to interact with the computer. The following is a simple form to calculate square value of a given number.

Example UserForm

You can enter the any numerical value in TextBox and Push the Command Button to see the result (Example file is attached in this post).
userform-Example2

What Are UserForm Controls or ActiveX Controls?

UserForm Controls are objects which you can be placed onto UserForms to interact with your data. There are several ActiveX controls which help users to do different activities with data, each control have different functionality.

We can place form controls on user forms based on our requirement, then add the code for each control to perform required tasks. Following are most commonly used control and their uses.

What Are The Different UserForm Controls & Use?

You can click on the ToolBox to see the various controls available in the VBE (Visual Basic Environment).

userform-ToolBar

Control Control Name Description
userform-ToolBar1 Label You can use this contol to display the text on the userform
userform-ToolBar2 Text box Enable user to enter some text or data
userform-ToolBar8 Command button Push Button, uses to runs a macro that performs an action when a user clicks it
userform-ToolBar3 Combo Box Drop-down list can be used to provide the interface to select one item from the list of items
userform-ToolBar4 List Box List Box can be used to provide the interface to select one or more item from the list of items
userform-ToolBar8 Frame Layout element which groups common elements
userform-ToolBar6 Option Button Allow user to select an exclusive option from the list of choices
userform-ToolBar5 Check Box Allow user to select one ore more options from the list of choices
userform-ToolBar15 Image You can use this to display a image on the userform

You can add more control to the toolbox dialog by right clicking on the toolbox dialog.

userform-ToolBar-moreControls

Forms and Controls in Excel VBA – Practical Learning: Developing A Simple UserForm

Now we will develop a simple userform, follow the below steps to create a userform to Find Square Values of a given number.

Step 1: Open VBE by pressing Alt+F11

userform-pl1

Step 2: Goto Menu Bar -> Insert -> Click on UserForm

userform-pl2

It should look like this:
userform-pl3

Step 3: Click On ToolBox and Add Three Labels, One TextBox and Two Command Buttons as shown below

userform-pl4

userform-pl5

Step 4: Now Click On the First Label and Change the Caption of the Label as “Enter a Value” – as shown below

userform-pl6

Similarly, change the caption of second Label as “Square Value”, Caption of the Third Label as blank(just delete the captions, we need this blank label to show the square value of the given value), Command Button1 as “Find Square Value”, Command Button2 as “Exit”, it should look like this:
userform-pl7

Step 5: Now Double Click On the First Command Button (Find Suare Value), It will take you to the form code module, place the following code
Private Sub CommandButton1_Click()
'Calculate Square Value
Label3.Caption = TextBox1.Value * TextBox1.Value
End Sub

Similarly add the following code for Exit Button:

Private Sub CommandButton2_Click()
'Exit Form
Unload Me
End Sub

Now- your code module should look like this:
userform-pl8

Step 6: Click on the Useform (left pane) to view the designed Form and Click on the Run Button to test it

userform-pl9

userform-pl10

Step 7:You can insert an ActiveX Command Button in the Worksheet and Add the following code to call the userform from your Worksheet
Private Sub CommandButton1_Click()
'Call userform
UserForm1.Show
End Sub

Example File

userform-pl11

mongopono.ru – Simple UserForms

LIMITED TIME OFFER
By |May 25th, 2013|Excel VBA|6 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

6 Comments

  1. Ali February 23, 2015 at 10:21 AM - Reply

    Hi sir, loved your website! The only thing I would like to point out is that you’ve interchanged the icons for Command buttons and Frame in your indroduction. Might get confusing for newbies!

    Thanks and keep up the good work!
    Ali!

    • PNRao March 2, 2015 at 7:44 PM - Reply

      Thanks lot Ali- Changed now! PNRao!

  2. Tracey-lee March 5, 2015 at 11:35 PM - Reply

    Thank you soooo much for all the information that you have shared as well as the time. I am a newbie and really appreciate your site.

  3. Naresh August 31, 2015 at 8:38 PM - Reply

    Thank u very much sir…i’m new for this website but i noticed that u care of our request or comments…so once again thank u…

    • PNRao August 31, 2015 at 11:06 PM - Reply

      You are most welcome Naresh! I am glad you found this useful.
      Thanks-PNRao!

  4. November 19, 2015 at 9:10 PM - Reply

    Thank you verrrry much. most of the time I find what am looking for in your site. God bless you.

Leave A Comment


Related pages


excel coding basicsvlookup lock table arrayexcel to csv convertercheckboxes excelwidgets for excelvba select last rowsas macros basicsrun a macro in excelvba unprotect all sheetsexcel formulas pdf with example 2007 in hindishow developer tab in word 2010combobox excel 2013excel 2007 autosave locationcan you merge cells in excelwhat is sumif function in excelexcel 2007 iferrorms access vba message boxexcel vba add new worksheetfor each in excel vbavba object propertieshow to merge two cells together in excelhow can i merge two columns in excelfind duplicates in excel spreadsheettesting interview questions for freshers with answersrange cells vbacomment excel vbauseful vb scriptsvba lcaseexcel vba empty cellvisual basic editor in excel 2007hyperlinks examplesexcel macro columndml examplesisblank in exceluseful vbaworkbooks open filename vbavba casesunprotect password excelvba macro examplevba command button clickhow to create vlookup in excel 2010vba create objectvba repeat untilsumif function in excel 2010lookup formulas in excelexcel timevaluevba excel stringhow to change xml to excelshortcut to insert column in excelhide sheets in excel vbaexcel insert worksheethow to make pivot tables in excel 2007excel macro to open fileexcel option button valueworkbooks in excelunlock cells excelexcel vba find lookinvba formulavba application.displayalertsdashboard formats in excelmatch type excelexcel forgot password to unprotect sheethow to lock an excel file 2007example of countifexcel startup macrohow to turn on developer tab in excel 2010what is macros in excel 2007excel checkbox macrofree capacity planning template excelexcel formulas 2007 with examplespivot table vbafor loop in vbaactivecell.row