Excel Macro Tutorial : Excel Form

.

In this article, I am going to explain every aspect of the VBA Form. User forms in Excel are really nice to see. It looks very fascinating while working with User Forms. You can build a very nice UI (User Interface) using Excel VBA Form. In this article, you are going to learn the following things about VBA User Form:


1. How to Create User Form (Creating VBA Form)
2. Showing a VBA Form (Display an Excel Form)
3. How to Initialize a UserForm
4. Hiding a VBA Form
5. Unloading VBA Form
6. Closing VBA Form
7. Example: Creating Data Entry Form using Excel Form
8. Free Download: Data Entry Form using Excel Form

1. How to Create User Form (Creating VBA Form)

Creating a Form (Designing a Form) in excel is very easy and interesting. For Designing a Form a single Line Code is not required. Code is required only to make that Form function as per your requirement.
To create a Form, follow the below simple steps:

Step 1. Open your Workbook and Press Alt + F11
Step 2. VB Code Editor will open as below

User Form - VBE Editor

Step 4. Right Click on Microsoft Excel Object
Step 5. Go to Insert –> User Form

User Form - Add-New-Form

Step 6. In Right Side you can see one Default UserForm1 Created. You can see the ToolBox as well with all the Controls available for the User Form.

User Form - Add-New-Form-With-Tool Box

Step 7. Using the Tool Box of the User Form you can Drag and Drop what all Controls you want on your form.
Refer the below User form which I have created to show as an example. You can design by your own how you want.

User Form - Data Entry Form

2. Showing a VBA Form (Display an Excel Form): As you have seen we have created the User Form in VBA Code. User form will not be displaying on the Excel Sheet by default. To display the form we need to write a piece of code. This is just a single statement to launch an Existing User Form. You can execute that statement to open User form.


UserForm1.Show

You can add the above statement where you want to launch your user form. UserForm1 is the name of the User Form Control.

3. How to Initialize a VBA Form:

Before getting in to technical details lets discuss, what is Initialization of User form? Initialization of User Form is nothing but to do some default operation while launching the Form. At the time of loading the User Form, we set certain values or any kind of defaulting to any field, comes under Initialization.

Whatever code is there in UserForm_Initialize() Event of User Form, will be executed as soon as User Form will be launched (Shown).
Click on the User Form and you will be taken to the below Private Sub with Initialize Event as shown in Image below:

VBA Form - Initialization

Example:



Private Sub UserForm_Initialize()

UserForm1.ComboBox1.Clear
 With UserForm1.ComboBox1
    .AddItem "Junior Engineer"
    .AddItem "Engineer"
    .AddItem "Team Lead"
    .AddItem "Technical Lead"
    .AddItem "Functional Consultant"
    .AddItem "Technical Consultant"
    .AddItem "Project Manager"
    .AddItem "Delivery Manager"
    .AddItem "Group Delivery Manager"
    .AddItem "Unit Head"
    .AddItem "Regional Head"
 End With
End Sub

4. Closing and Hiding and Unloading a VBA Form:

Like windows dialog box User Form also has a by default Cross Button to close the form. It will simply close the User form. There is a difference between Closing and hiding a user form.

Closing a User form will close the User Form. All the value entered in to that form will be cleared (lost) when you launch it again. Whereas on hiding a user form all the vaues entered in to that form will remain there and can be seen again once you launch the user form again. Both the terms (Closing and hiding) are pretty much self explanatory.

Closing or Unloading an Excel User form are same. In VBA Code, to close an Excel Form, Unload keyword is used. This is the reason we call it both either closing a VBA Form or Unloading a VBA Form.

Now you know the about Closing, Hiding and Unloading an Excel UserForm. Now i will show you the VBA syntax for Unloading (Closing) and Hiding with an Example.

Hiding a User Form:
i) Syntax:
<UserForm Name>.Hide
ii) Example:
Private Sub UserForm_Click()
UserForm1.Hide
End Sub

User Form - How to hide

Unloading or Closing a User Form:
i) Syntax:
Unload <User Form Name> OR Unload Me
ii) Example:
Private Sub UserForm_Click()
Unload UserForm1 OR Unload Me
End Sub

5. Example: Creating Data Entry Form using Excel Form:

Now its time for doing some hands-on. I have created one Simple Data Entry Form using Excel VBA Form as shown below in Image. If you wish to see the Code and the whole Workbook download it from below:

Employee Data Entry Form - Excel VBA

Employee Data Entry Form – Excel VBA

Download FREE - Employee Data Entry Form

To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial

Download this FREE Excel workbook to practice.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

34 Comments

  1. Marcelo Liascovich

    Thank you very much. I would like to receive THE code AND the whole Workbook

    Bye AND HAVE a Good day

    Reply
    • Vishwamitra Mishra

      Thanks Marcelo.

      I am glad that Article seems to be helpful to you. Would like to hear from you frequently.

      Excel Workbook with Code, is sent to you.

      Vish

      Reply
  2. nur

    Dear Vish Bhaia, I am new in excel macro (2003) , by reading your website I am keen interested to learn excel macro. Your article regarding “Excel Macro Tutorial : Excel Form” very much interesting. I would like to get this code as well as whole work book . and also accept me as registered user earlier I applied for this .

    Nur …Bangladesh

    Reply
    • Vishwamitra Mishra

      Hi Nur,

      Thanks for the appreciation and i am glad that you liked this article.

      Workbook with this Code is sent to your email ID. Please check and get back to me incase of any doubt or issue.

      To register yourself follow the below steps:

      1. Enter your email address at the Top of the Website and Click on "Join Now".

      2. One mail will be sent to your mail box.

      3. Open the mail and Confirm the subscription by clicking on the link given inside the mail.

      You are done now. You will start getting Regular updates directly to your mailbox.

      Reply
  3. Nur

    Dear Vish Bhaia, Thanks for reply.I didn’t get your mail (any work book and macro code regarding “Excel Macro Tutorial : Excel Form”.) Please send me again.

    Reply
    • Vishwamitra Mishra

      Re-sent !! Please confirm

      Reply
      • praveen kumar

        need macro code ebook sir…

        Reply
        • Vishwamitra Mishra

          Hi Praveen,
          Once you subsribe to the website, you will automatically receive an email with a link to download the ebook.

          Reply
  4. Nur

    Dear Vish Bhaia, yea I got it, thanks for giving . But one problem , still I can’t open it as it was .xlsm formatted (2007). But currently I am using 2003. I tried 3 to 4 hours to convert it xlsm to xls with the help of online converter as well as some free converter but the converted file is unreadable format. Still I am searching good online converter site as well as free .xlsm to xls converter in the internet. As I am very much thirsty to learn macro !!!

    Thanks ..Nur ..Bangladesh

    Reply
    • Vishwamitra Mishra

      Hi Nur,

      Excel 2003 format is sent !! Please confirm

      Reply
  5. Nur

    Dear Vish Bhaia, Sorry for late response, it is working perfectly. Thanks

    Reply
  6. Nur

    Dear Vish Bhaia, again pain !!, the problem is when I try to add new entry
    It was showing “Run time error’1004’ Method”range of object_global’ failed.
    I need your help please..

    Reply
    • Vishwamitra Mishra

      Nur,
      For such error issues, kindly paste your code or module where error is coming.

      Reply
  7. Nur

    Public Emp As Integer

    Public iRow As Integer

    Sub initialize()

    Call get_Next_Emp

    UserForm1.ComboBox1.Clear

    With UserForm1.ComboBox1

    .AddItem "Junior Engineer"

    .AddItem "Engineer"

    .AddItem "Team Lead"

    .AddItem "Technical Lead"

    .AddItem "Functional Consultant"

    .AddItem "Technical Consultant"

    .AddItem "Project Manager"

    .AddItem "Delivery Manager"

    .AddItem "Group Delivery Manager"

    .AddItem "Unit Head"

    .AddItem "Regional Head"

    End With

    UserForm1.OptionButton1.Value = 1

    UserForm1.ComboBox2.Clear

    With UserForm1.ComboBox2

    .AddItem "Graduation"

    .AddItem "Post Graduation"

    .AddItem "PHD"

    End With

    End Sub

    Sub get_Next_Emp()

    If Range("A6").Value = "" Then

    Emp = 1000

    ElseIf Range("A7").Value = "" Then

    Emp = 1001

    Else

    Range("A6").Activate

    Emp = Range("A" & Range(Selection, Selection.End(xlDown)).Cells.Count + 5).Value + 1

    End If

    End Sub

    Sub populate_value(iRow As Integer)

    Range("B" & iRow).Value = UserForm1.TextBox1.Value ' during adding any new entry I got problem here as i mention above. i can modify or delete entry but problem for adding new entry'

    Range("C" & iRow).Value = UserForm1.TextBox2.Value

    Range("D" & iRow).Value = UserForm1.TextBox4.Value

    Range("A" & iRow).Value = UserForm1.TextBox5.Value

    Range("F" & iRow).Value = UserForm1.TextBox6.Value

    Range("E" & iRow).Value = UserForm1.TextBox7.Value

    Range("G" & iRow).Value = UserForm1.ComboBox1.Value

    Range("H" & iRow).Value = UserForm1.ComboBox2.Value

    End Sub

    Sub iDelete(iRow As Integer)

    Application.Rows(iRow).Delete

    End Sub

    Reply
  8. sarang thatte

    Dear Mr Vishwamitra,

    I have a small problem with the combo Box in Excel USER FORM . The names of students are in a DROP DOWN Combo Box . once the user clicks on a particular name chosen from the combo box : the other details in that row pertaining to marks in various subjects for that student need to be shown in different text boxes created on the form. In Access using as Database and VB as the coding the code was simple as :

    Private Sub Combo2_Click()
    Dim sql1 As String
    sql1 = “select ieng from imarks where iname = ‘” + Combo2.Text + “‘”
    Set rs2 = db.OpenRecordset(sql1, dbOpenDynaset)
    Text2.Text = rs2!ieng
    Text3.Text = rs2!imath
    Text4.Text = rs2!iphy
    Text5.Text = rs2!ichem

    rs2.Close
    End Sub

    But I am having difficulty in doing it EXCEL cauz the fields are not defined … Could u halp me with some coding … take four subjects and five students in drop down combo box …. Thanks in advance ….

    Sarang Thatte, New Delhi

    Reply
    • Vishwamitra Mishra

      Hi,

      Can you please let me know, what error are you getting while trying it in Excel VBA.

      Thanks,
      Vish

      Reply
  9. Suthchayavee

    Thank you

    Reply
  10. Raouf Malek

    Many thanks

    Reply
    • Vishwamitra Mishra

      Thanks Raouf !!

      Reply
  11. poliboy

    Private Sub CommandButton1_Click()

    If OptionButton1.Value = True Then

    If Range("A6").Value = "" Then

    iRow = 6

    ElseIf Range("A7").Value = "" Then

    iRow = 7

    Else

    Range("A6").Activate

    iRow = Range(Selection, Selection.End(xlDown)).Cells.Count + 6

    End If

    End If

    If OptionButton3.Value = True Then

    Call iDelete(iRow)

    Else

    Call populate_value(iRow)

    End If

    Unload UserForm1

    End Sub

    Private Sub CommandButton2_Click()

    Unload Me

    End Sub

    Private Sub OptionButton1_Click()

    If OptionButton1.Value = True Then

    TextBox5.Enabled = False

    TextBox5.Text = Emp

    CommandButton1.Caption = "Save"

    End If

    End Sub

    Private Sub OptionButton2_Click()

    If OptionButton2.Value = True Then

    TextBox1.Enabled = True

    TextBox2.Enabled = True

    TextBox4.Enabled = True

    TextBox5.Enabled = True

    TextBox6.Enabled = True

    TextBox7.Enabled = True

    'CommandButton4.Enabled = True

    'CommandButton2.Enabled = True

    ComboBox1.Enabled = True

    ComboBox2.Enabled = True

    CommandButton1.Caption = "Edit"

    End If

    End Sub

    Private Sub OptionButton3_Click()

    If OptionButton3.Value = True Then

    TextBox1.Enabled = False

    TextBox2.Enabled = False

    TextBox4.Enabled = False

    TextBox5.Enabled = True

    TextBox6.Enabled = False

    TextBox7.Enabled = False

    'CommandButton4.Enabled = False

    'CommandButton2.Enabled = False

    ComboBox1.Enabled = False

    ComboBox2.Enabled = False

    CommandButton1.Caption = "Delete"

    Else

    TextBox1.Enabled = True

    TextBox2.Enabled = True

    TextBox4.Enabled = True

    TextBox5.Enabled = True

    TextBox6.Enabled = True

    TextBox7.Enabled = True

    CommandButton4.Enabled = True

    CommandButton2.Enabled = True

    ComboBox1.Enabled = True

    ComboBox2.Enabled = True

    End If

    End Sub

    Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If OptionButton2.Value = True Or OptionButton3.Value = True Then

    Range("A6").Select

    Range(Selection, Selection.End(xlDown)).Select

    If (Selection.Find(What:=TextBox5.Value, After:=ActiveCell, LookIn:=xlFormulas, _

    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

    MatchCase:=True, SearchFormat:=False)) Is Nothing Then

    TextBox4.Value = ""

    TextBox1.Value = ""

    TextBox2.Value = ""

    TextBox7.Value = ""

    TextBox6.Value = ""

    MsgBox ("No Record Found")

    Else

    iRow = (Selection.Find(What:=TextBox5.Value, After:=ActiveCell, LookIn:=xlFormulas, _

    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

    MatchCase:=True, SearchFormat:=False).Row)

    TextBox4.Value = Range("D" & iRow).Value

    TextBox1.Value = Range("B" & iRow).Value

    TextBox2.Value = Range("C" & iRow).Value

    TextBox7.Value = Range("E" & iRow).Value

    TextBox6.Value = Range("F" & iRow).Value

    ComboBox1.Value = Range("G" & iRow).Value

    ComboBox2.Value = Range("H" & iRow).Value

    With UserForm1

    TextBox5.SetFocus

    End With

    End If

    End If

    End Sub

    Private Sub UserForm_Initialize()

    Call initialize

    End Sub

    Hi,

    in this code why is it not working when i put it in command button?

    could you help me please..

    Reply
  12. Premkumar G

    Hi Vish,

    Am premkumar from Bangalore. I want to learn macro programming. can u assist me or send me link to learn step by step.

    Is it possible to do programming for me. I need only one.

    Reply
  13. Premkumar G

    sry

    later i understood.

    am already intouch with you.

    thanks

    Reply
  14. Agha

    Hi,

    Nice coding and descriptive…

    keep it up dear.

    Reply
    • Vishwamitra Mishra

      Thanks Agha !!

      Reply
  15. rajaudaiyar

    Hi Sir,

    Am Raja from Chennai. I want to learn macro programming. can u assist me or send me link to learn step by step.

    Is it possible to do programming for me. I need only one.

    Reply
  16. bg

    Hi Sir,

    Am bg from Mumbai. I want to learn macro programming. can u assist me or send me link to learn step by step.

    Awaiting a positive reply.

    Reply
    • Vishwamitra Mishra

      Dear BG,
      You can find the tutorials for beginners in tutorial section. Also you can get an E-Book for beginners after subscribing to this website.

      If you need any specific article or help, write to me.. I will be more than happy to help you !!

      Reply
      • MURALI

        I like to learn Macros………. need guideline from basic…

        Pls help…

        Reply
  17. Mugunth

    Hi Vishwa,

    I’m having doubt in creating User form,in that designation field you have added several items for eg(Engineer,Team lead).How you added ??Is that code is recorded using Record macro or manually edited coding.If so how to do it.Could you please explain on it?

    Reply
  18. Ashwini

    Hi,

    I need to learn coding in macros to create user forms;

    The required fields are:

    Sl. No. Rainbow series Part Number Number of Boards Delivered Items Distributor Name & Contact Customer Name Reference PO details PO Value Shipping address Billing details Commited shipping date (MM/DD/YYYY) Latest Commited shipping date (MM/DD/YYYY) Shipping date No of days left Basis Status Remarks

    Kindly support on this

    Reply
  19. Clarissa

    Thanks for this amazing project

    Reply
  20. Freddie

    Hi, I love the colour scheme you use here, how do I set my fonts and backgrounds to the exact set you are using? Thanks! Freddie

    Reply
  21. Pravallika

    HI Sir,

    I want to learn Macro coding step by step in clear way can u send me the link or online book to my mail.

    Thank you

    Reply

Trackbacks/Pingbacks

  1. VBA Code - Change the Case of existing Texts in a Sheet - Let's excel in Excel - […] Excel Macro Tutorial : Excel Form […]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest