Home > Macros >UserForms >Basic Create an Excel UserFormTo make it easier for users to enter data in a workbook, you can create an Excel UserForm. Written instructions and step-by-step videos in this tutorial show you the steps. See also Excel UserForm with Combo Boxes and Basic UserForms - Videos |
Video: Basic UserForm - DemoBefore 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 WorksheetTo start from scratch, follow the steps below:
This is the information that you will collect in the UserForm. Create Named Table
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 EnabledNext, to save the file, follow these steps:
|
Show Developer TabUserForms 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. |
Open Visual Basic EditorNow 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:
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.
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.
|
Create a UserFormNow that the VBE is set up, you can follow the steps below, to create a UserForm in the PartsDbText01.xlsm workbook
A blank UserForm appears, and the Toolbox should open.
You will use the tools on the Controls tab in the Toolbox to add controls, such as buttons, to the UserForm |
Add First Textbox to UserFormThe 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:
|
Add Buttons to UserForm
|
UserForm Add Button CodeCopy the code below, without the Private Sub and End Sub lines, and paste it into the UserForm, as shown in the video above.
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 WorksHere are a few details on how the Add button code works, if you're interested. VariablesAt the start of the code, two variables are defined.
Dim iRow As Long
Dim ws As Worksheet Set ws = Worksheets("PartsData") Step 1When 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 2Before 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 3Next, 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 4Finally, 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
Private Sub cmdClose_Click() Unload Me End Sub
To allow users to close the form by pressing the Esc key: To prevent users from closing the form by clicking the X buttonWhen 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.
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
Now, if someone clicks the X in the UserForm, they'll see your message. |
Test the UserFormThis video shows how to test the UserForm textboxes and buttons. |
Finish the WorkbookThis video shows how to add a button to the worksheet, to open the UserForm, and hide the database sheet. |
Get the Sample File
Related TutorialsExcel UserForm Search Add Edit |
Last updated: October 15, 2022 4:04 PM