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
Step 4. Right Click on Microsoft Excel Object
Step 5. Go to Insert –> User 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.
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.
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:
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 |
|
|
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
Download FREE - Employee Data Entry Form
Download this FREE Excel workbook to practice.
Thank you very much. I would like to receive THE code AND the whole Workbook
Bye AND HAVE a Good day
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
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
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.
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.
Re-sent !! Please confirm
need macro code ebook sir…
Hi Praveen,
Once you subsribe to the website, you will automatically receive an email with a link to download the ebook.
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
Hi Nur,
Excel 2003 format is sent !! Please confirm
Dear Vish Bhaia, Sorry for late response, it is working perfectly. Thanks
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..
Nur,
For such error issues, kindly paste your code or module where error is coming.
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
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
Hi,
Can you please let me know, what error are you getting while trying it in Excel VBA.
Thanks,
Vish
Thank you
Many thanks
Thanks Raouf !!
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..
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.
sry
later i understood.
am already intouch with you.
thanks
Hi,
Nice coding and descriptive…
keep it up dear.
Thanks Agha !!
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.
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.
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 !!
I like to learn Macros………. need guideline from basic…
Pls help…
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?
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
Thanks for this amazing project
Can u help me, how to make code excel macro for this picture http://i1169.photobucket.com/albums/r501/ales1283/inputReport_zpst6pfpmt8.png
Istruction:
How to connect form Input and Form Report with Button “SIMPAN” or Save
Thankyou for help ^_^
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
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