Excel Macro Tutorial : Excel Form

Dear Beginner,

In this article I am going to explain every aspects of VBA Form. User forms in Excel is 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 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

User Form - VBE Editor



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


User Form - Add-New-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

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

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

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

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

Employee - Data Entry Form




Download Now


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

Related posts:

  1. Excel Macro Tutorial : Toggle Button in Excel Some times, we require a single control or Button which...
  2. Excel Macro Tutorial : ComboBox in Excel Macro Hello Friends,   It’s been long time, i posted any...
  3. Excel Macro Tutorial : How to Use ListBox in Excel As we saw in the Previous Article you learnt How...
  4. Excel Macro Tutorial : VBA Spin Button In the previous Article How to write Excel Macro –...
  5. Excel Macro Tutorial : How to add different types of controls like Text Box, Radio button, button etc. in Excel Dear Readers, This is the 3rd Article of this Excel...

26 Responses to “Excel Macro Tutorial : Excel Form”

  1. Mugunth says:

    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?

  2. bg says:

    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.

    • Vishwamitra Mishra Vishwamitra Mishra says:

      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 !!

  3. rajaudaiyar says:

    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.

  4. Agha says:

    Hi,

    Nice coding and descriptive…

    keep it up dear.

  5. Premkumar G says:

    sry

    later i understood.

    am already intouch with you.

    thanks

  6. Premkumar G says:

    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.

  7. poliboy says:

    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..

  8. Raouf Malek says:

    Many thanks

  9. Suthchayavee says:

    Thank you

  10. sarang thatte says:

    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

    • Vishwamitra Mishra Vishwamitra Mishra says:

      Hi,

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

      Thanks,
      Vish

  11. Nur says:

    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

  12. Nur says:

    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..

  13. Nur says:

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

  14. Nur says:

    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

  15. Nur says:

    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.

  16. nur says:

    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

    • Vishwamitra Mishra Vishwamitra Mishra says:

      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.

  17. Marcelo Liascovich says:

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

    Bye AND HAVE a Good day

    • Vishwamitra Mishra Vishwamitra Mishra says:

      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

Leave a Reply

Comments

comments