How do I insert data into an Excel form?

Introduction to UserForms

To create a UserForm requires some programming, and you can see the steps in the videos shown below, and in the written instructions. In this tutorial:

• see how to build a basic UserForm in Excel, with text boxes for data entry
• nventory data is stored on a hidden worksheet, where it is protected from accidental damage or deletion.
• Users enter inventory data by opening the UserForm, filling in the boxes, and clicking a button.
• In the completed workbook, click a worksheet button, and a macro opens the UserForm.

Video: Basic UserForm - Demo

Before you start to build a Data Entry UserForm with Text Boxes, you can watch this short video that shows the completed form, and how it works.

You'll see how to enter data in the form, and then send that data to a hidden worksheet.

Set Up Worksheet

In this example, a parts inventory is stored on a hidden worksheet in a Microsoft Excel file..

This video shows how to create the Excel file, and set up the table where the data will be stored.

Set Up Worksheet

To start from scratch, follow the steps below:

• Open a new, blank workbook
• Double-click on the sheet tab for Sheet1
• Type PartsData as the sheet name, then press Enter, to complete the name
• In cells A1:D1, enter the headings for the parts inventory database, as shown in the screen shot below – PartID, Location, Date, Qty.

This is the information that you will collect in the UserForm.

Create Named Table

• Next, to create a named table, select one of the heading cells, and on the Ribbon's Insert tab, click the Table command
• In the Create Table dialog box, make sure that the data range is $A$1:$D$1, and add a check mark to the box for My table has headers, then click OK

The headings, and row 2, have been converted to a named table, with drop down arrows in the heading cells, and automatic formatting

When you select a cell in the named table, you will see a Design tab on the Ribbon, under the TableTools tab

Save Workbook Macro Enabled

Next, to save the file, follow these steps:

• Click the Ribbon's File tab, and click Save.
• Select a folder, and in the File name box, enter the name PartsDbText01
• From the Save as Type drop down, select Excel Macro-Enabled Workbook (*.xlsm)
• Click the Save button, to save the Excel file.

Show Developer Tab

UserForms are created in the Visual Basic Editor (VBE), where you can see the Visual Basic for Applications (VBA) code.

Before you start working in the VBE, check the Excel Ribbon, to see if it shows the Developer tab.

If you do NOT see a Developer tab on the Excel Ribbon, follow the steps in this video, to show the Developer tab in Excel.

Create UserForm

UserForms are created with Excel VBA programming, in the Visual Basic Editor.

This video shows how to create the UserForm, and give it a name and a caption. The written steps are below the video.

Open Visual Basic Editor

Now you're ready to get started on the UserForm, so you will open the VBE window

To open the Visual Basic Editor, follow these steps:

• On the Excel Ribbon, click the Developer tab
• In the Code group, click Visual Basic.
  • Tip: The keyboard shortcut to open the VBE is Alt+F11

The VBE opens, and at the left, you should see the Project Explorer, which lists all the workbooks you have open – even the hidden workbooks, such as add-ins.

• Tip: If you do not see the Project Explorer, click the View menu, then click Project Explorer, to open it.

You will be adding a UserForm to the PartsDbText01.xlsm workbook, and you should see its name in the list, preceded by "VBAProject".

You will also be using the Properties Window, which is usually shown below the Project Explorer.

• Tip: If you do not see the Properties Window, click the View menu, then click Properties Window, to open it.

Create a UserForm

Now that the VBE is set up, you can follow the steps below, to create a UserForm in the PartsDbText01.xlsm workbook

• In the Project Explorer, select the PartsDbText01.xlsm project
• In the VBE menu bar, click Insert, then click UserForm

A blank UserForm appears, and the Toolbox should open.

• Tip: If you don't see the Toolbox, click the Toolbox button on the menu (circled in the screen shot below).

You will use the tools on the Controls tab in the Toolbox to add controls, such as buttons, to the UserForm

Name the UserForm

When you add a UserForm to the workbook, it is automatically selected in the VBE.

• There is a handle at each corner of the UserForm, and at the centre on each side
• You can drag any handle in or out, to change the UserForm's size.

Before you add any controls to the UserForm, you will give it a name, and change the caption in its Title Bar.

To name the UserForm, follow these steps:

• With the UserForm selected, go to the Properties window
• At the top of the list, double-click on the (Name) property
  • The default name – UserForm1 – will be selected
• Type frmPartLoc as the new name for the UserForm, and press the Enter key.

After you change the form's name, you'll see two changes in the Visual Basic Explorer

• The form name changes in the Project Explorer
• When the UserForm is selected, its name appears in the Title Bar of the Property Window.

Change UserForm Title Bar

Next, to change the caption in the UserForm's Title Bar, follow these steps:

• With the UserForm selected, double-click on the default caption – UserForm1 – in the Properties Window.
• Type Parts Inventory and press the Enter key

• The UserForm's Title Bar will display the new caption.
• Finally, click the Save button in the VBE toolbar, to save the changes that you've made to the Excel file

Add Textboxes to UserForm

The next step is to add four textboxes on the UserForm, where the data will be entered. A label will be added beside each textbox, to identify it.

To see how to add Textboxes to the UserForm, you can watch this short video. The written instructions are below the video

Add First Textbox to UserForm

The objects on a UserForm, such as buttons, and textboxes, are called controls.

To allow users to enter data, you can add textbox controls to the form, with label controls to describe them.

In this workbook's PartsData sheet, there are four columns (fields) – PartID, Location, Date and Qty.

In the UserForm, you will add a text box for each of these columns.

Follow these steps to add the first text box, for the PartID field:

• In the Toolbox, click on the TextBox button
• On the UserForm, click near the top centre, to add a standard-sized textbox

• With the new textbox selected, double-click on the Name property in the Properties window.
• To change the default name, type txtPart and press the Enter key

Add a Label to the UserForm

To help people enter data, you can add label controls beside each of the textboxes, so it is clear what should be entered in each textbox.

Follow these steps to add a label for the first textbox:

• In the Toolbox, click on the Label button

• On the UserForm, click about 1" to the left of the textbox, to add a standard sized label.

• If necessary, you can resize the label, so it doesn't cover the textbox -- point to the handle on its right border, and drag to the left

With the new label selected, double-click on the Caption property in the Properties window.

Type Part and then press the Enter key

The UserForm now has one textbox and label, for the PartID field.

Add remaining textboxes and labels

You'll add three more textboxes and labels to the UserForm. A quick way to create additional textboxes is to copy the existing textbox, click on the UserForm, and paste.

Follow these steps to create the additional textboxes and labels:

• Click on the Part label to select it
• Press the Ctrl key, and click on the Part textbox, to also select it
• On the Edit menu, click Copy (or use the Ctrl + C shortcut)
• On the Edit menu, click Paste (or use the Ctrl + V shortcut)
• Drag the pasted textbox and label down, if necessary, so they are below the first textbox and label.
• Repeat steps 4 and 5, twice, to paste and position two more textboxes and labels. If necessary, make the UserForm larger, by dragging down on the handle on its bottom border.

Name the New Textboxes

Next, follow the steps below, to name each of the new textboxes, and change its label's caption.

• Click on the second textbox, and change its name to txtLoc.
  • Change its label caption to Location.
• Click on the third textbox, and change its name to txtDate.
  • Change its label caption to Date.
• Click on the fourth textbox, and change its name to txtQty.
  • Change its label caption to Quantity

Align Textboxes and Labels

If the textboxes are not aligned, you can follow these steps to align them:

• Click on one of the textboxes
• Hold the Ctrl key, and click on the remaining textboxes
• Click the Format menu, then click Align, and click Lefts
• NOTE: The selected controls will align with the control that has the white handles – the one that was selected last

• Next, repeat the steps abovc, to align the labels
• Finally, save the workbook

Add Buttons to UserForm

To allow users to perform an action, you can add command buttons to the UserForm.

This form will have 2 buttons:

• one button to add data to the database
• another button to close the form.

This video shows how to add buttons to the UserForm, and the code that runs when you click those buttons.

Notes:

• The video shows how to copy the code from a PDF file, and paste it into the UserForm.
• Instead, copy the code from the written instructions below

Add Buttons to UserForm

• In the Toolbox, click on the CommandButton button.

• On the UserForm, click at the bottom left, to add a standard sized CommandButton.
• With the new CommandButton selected, double-click on the Name property in the Properties window.
• Type:  cmdAdd
  and press the Enter key
• With the new CommandButton selected, double-click on the Caption property in the Properties window.
• Type:  Add this part
  and press the Enter key
• Click on an empty part of the UserForm, to select the UserForm and to display the Toolbox.
• Repeat the above steps to add a CommandButton named cmdClose, with a label Close

• If required, you can reposition the buttons by dragging them to a new location on the UserForm. 
  

Add code to the buttons

To make the buttons perform an action, you create code that runs when the button is clicked.

Add code to the cmdAdd button

1. Select the cmdAdd button
2. On the Menu bar, choose View | Code. 
3. This creates a procedure, where you can add your code.
   

4. Where the cursor is flashing, copy and paste the code from the section below.

UserForm Add Button Code

Copy the code below, without the Private Sub and End Sub lines, and paste it into the UserForm, as shown in the video above.

• NOTE: In some places, you’ll see “xl” in the code, such as “xlRows”.
  • The characters after the x is a lower-case “L”, not the number 1.

There is an explanation of how this code works, in the next section.

NOTE: After you paste the code, on the Menu bar, choose View | Object, to return to the UserForm

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a part number
If Trim(Me.txtPart.Value) = "" Then
  Me.txtPart.SetFocus
  MsgBox "Please enter a part number"
  Exit Sub
End If

'copy the data to the database
'use protect and unprotect lines, 
'     with your password
'     if worksheet is protected
With ws
'  .Unprotect Password:="password"  
  .Cells(iRow, 1).Value = Me.txtPart.Value
  .Cells(iRow, 2).Value = Me.txtLoc.Value
  .Cells(iRow, 3).Value = Me.txtDate.Value
  .Cells(iRow, 4).Value = Me.txtQty.Value
'  .Protect Password:="password"
End With

'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus

End Sub

How the Add Button Code Works

Here are a few details on how the Add button code works, if you're interested.

Variables

At the start of the code, two variables are defined.

• The iRow variable stores the number of rows on the PartsData worksheet

Dim iRow As Long

• The ws variable is set as the PartsData worksheet, where the data will be stored.

Dim ws As Worksheet
Set ws = Worksheets("PartsData")

Step 1

When the new data is added to the database, it should go in the first empty row on the PartsData sheet. The code finds the last entry in the sheet, and gets its row number. Then 1 is added to that number, and stored in the iRow variable.

'find first empty row in database
  iRow = ws.Cells.Find(What:="*",  SearchOrder:=xlRows, _
  SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

Step 2

Before the new data is added to the database, the code checks to make sure that a part has been entered in the txtPart textbox.

'check for a part number
If Trim(Me.txtPart.Value) = ""  Then

If not, the txtPart textbox is selected.

Me.txtPart.SetFocus

A warning message is shown, and the procedure stops running. The record can't be added to the PartsData sheet, unless a Part has been entered.

MsgBox "Please enter a part number"
  Exit  Sub
  End If

Step 3

Next, the new data is added to the database, in the first empty row. The iRow variable stores that row number, and the other number in each line of code is the column number.

 'copy the data to the database
With ws
  .Cells(iRow, 1).Value = Me.txtPart.Value
  .Cells(iRow, 2).Value = Me.txtLoc.Value
  .Cells(iRow, 3).Value = Me.txtDate.Value
  .Cells(iRow, 4).Value = Me.txtQty.Value
End With

Step 4

Finally, the data is cleared from each of the textboxes, and the txtPart textbox is selected, so you are ready to enter another part, if desired.

'clear the data
Me.txtPart.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtQty.Value = ""
Me.txtPart.SetFocus

Add code to the cmdClose button

1. Select the cmdClose button
2. On the Menu bar, choose View | Code. 
3. Where the cursor is flashing, enter the following code:

Private Sub cmdClose_Click()
  Unload Me
End Sub

1. On the Menu bar, choose View | Object, to return to the UserForm.

To allow users to close the form by pressing the Esc key:

1. Select the cmdClose button
2. In the Properties window, change the Cancel property to True  
   

To prevent users from closing the form by clicking the X button

When the UserForm is opened, there is an X at the top right. In addition to using the Close Form button, people will be able to close the form by using the X. If you want to prevent that, follow these steps.

1. Right-click on an empty part of the UserForm
2. Choose View | Code
3. From the Procedure dropdown, at the top right, choose QueryClose

4. Where the cursor is flashing, paste the highlighted code from the following sample

Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the Close Form button!"
  End If
End Sub

1. On the Menu bar, choose View | Object, to return to the UserForm.

Now, if someone clicks the X in the UserForm, they'll see your message.

Test the UserForm

This video shows how to test the UserForm textboxes and buttons.

Test the UserForm

To test the form, you can run it from the VBE.

1. Click on an empty part of the UserForm, to select the UserForm and to display the Toolbox.
2. On the Menu bar, choose Run | Run Sub/UserForm.
   

3. In the Part textbox, type: 12345
4. Press the tab key to move to the next textbox.
5. When the textboxes have been filled in, click the 'Add this part' button.
6. Click the 'Close form' button, to return to the VBE.

If the tab order was incorrect (e.g. when you pressed the tab key you moved to the wrong textbox or button), you can change it

1. Right-click on an empty part of the UserForm
2. Choose Tab Order
3. Select a control in the list, and click the Move Up or Move Down button
4. Click OK 
   

Finish the Workbook

This video shows how to add a button to the worksheet, to open the UserForm, and hide the database sheet.

Create Button to open UserForm

To make it easy for users to open the UserForm, you can add a button to a worksheet.

1. Switch to Excel, and activate the PartLocDB.xls workbook
2. Double-click on the sheet tab for Sheet2
3. Type: Parts Data Entry
4. Press the Enter key
5. On the Drawing toolbar, click on the Rectangle tool (In Excel 2007 / 2010, use a shape from the Insert tab)
6. In the centre of the worksheet, draw a rectangle, and format as desired.
7. With the rectangle selected, type:
   Click here to add Part Information
   

8. Right-click on the rectangle border, and choose 'Assign Macro'
9. Click the New button
10. Where the cursor is flashing, type: frmPartLoc.Show
11. Go to the Excel window, and click the button, to open the UserForm.
• NOTE: While the UserForm is open, you won't be able to perform any other actions in Excel, such as entering data on the worksheet. You'll have to close the form first.
• If you want users to be able to perform other actions in Excel while the form is open, change the above line to: frmPartLoc.Show False to turn off the Modal setting 
  

Finish the Workbook

To finish the workbook, you can hide the sheet that contains the database.

1. Switch to Excel, and activate the PartLocDB.xls workbook
2. Select the PartsData sheet.
3. From the menu bar, choose Format | Sheet | Hide ( In Excel 2007 / 2010, use the Format commands on the Home tab)
4. Delete all other sheets, except Parts Data Entry
5. Click the Click here to add Part Information button, and enter your data into the database. 
6. Close and save the workbook
   

Videos: Create Excel 2003 User Form

The three videos below are older versions of the "Create Excel UserForm" instructions. In these videos, Excel 2003 was used, and the same steps can be followed in later versions.

Tip: When saving the file in Excel 2007 or later, save the Excel workbook as a macro-enable file type.

The Completed UserForm -- How It Works

Before building the UserForm to input data, you can watch this short video that shows the completed form, and how it works.

Creating a UserForm - Part 1

In part 1 of 3, you'll see how to create a blank Userform. Then you'll name the UserForm, and next you'll add text boxes and labels.

Users will be able to type data into the text boxes. Labels are added beside the text boxes, to describe what users should enter into the text box

Creating a UserForm - Part 2

In Part 2 of 3, you'll learn how to add buttons and a title on the UserForm.

With buttons on the UserForm, a user can click to make something happen. For example, click a button after entering data in the text boxes, when you're ready to move the data to the worksheet storage area.

Creating a UserForm - Part 3

In Part 3 of 3, you'll learn how to add VBA code to the controls, and you'll see how to test the UserForm.

The VBA code runs when a specific event occurs, such as clicking a button, or entering a combo box. In this example, the user will click a button, and the VBA code will move the data to the worksheet storage area.

How do I make a form fillable in Excel?

Can you create a form in Excel?

How do I paste data into a tabular form in Excel?

Where is data form in Excel?