Excel VBA - Using A UserFrom To Populate A Database
I've touched on building forms in a previous hub (Create A Basic Login Function) so will not go through how to build a form in this hub, but you can download a "Pre-Code" version of the workbook we will be discussing by clicking the link below. This workbook will be the best way of following this guide and learning how this works.
Once you have the Workbook UserForm Database Guide open, you may need to click a button at the top that enables editing, you may also need to click a button to enable Macros if it pops up.
Setting Up The Form To Talk To The Database
Currently, you should be looking at a single sheet workbook with a small Database which gathers personal information about a person. We will be using a Form to fill this information in.
Open the VBA editor (ALT + F11), you'll see in the Project Window in the top left that there is a Form called Add. Double click on Add and you'll see the Form we are going to use to populate the database.
I've already named all of the input fields with appropriate names:
- Name Field = Name
- Date Of Birth Field = DOB
- Gender Dropdown = Gender
- Email Field = Email
- Telephone = Telephone
- T&Cs Checkbox = TCs
These are the names that will be used in the code to reference the input boxes on the form.
Now it's time to start adding the code. We don't need the Form to do anything until the Submit button is pressed, so our code will reside within that button.
Double click on the Submit button, the code window will open up, pre-populated with code ready to detect when the Submit button is pressed.
All of our code must be added between the line Private Sub CommandButton1_Click() and End Sub. First we need to find the last row in the database that contains data, by finding this we can then tell VBA to place the Form data on the next line down. This is achieved with the following code:
Private Sub CommandButton1_Click() 'Check every cell for * (blank) 'Look in the values of the cells 'Look at the whole cell 'Search from bottom of file up 'Add 1 to the row, this will find 'the next empty row LastRow = Cells.Find( _ What:="*", _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchDirection:=xlPrevious).Row + 1 End Sub
Now you've made your form smart enough to know where the next empty row in the database is, the next step is to start populating it with the data from the form.
Lets start with moving the Name field on to the database. Add the following code above the End Sub (Line 17):
'The below line tells VBA we're only dealing 'with Sheet1 With Worksheets("Sheet1") 'Cells tells VBA that we're looking at 'a cell, the information inside the 'brackets is the row number and then 'the colum number. In this case we're 'using "lastrow" as the row number as 'thats the last row we worked out earlier. 'We then tell the value of the cell to 'be overwritten with the value in the 'Names section of the form .Cells(lastrow, 2).Value = Names.Value End With
You can test that this works now by clicking the little Play Button at the top of the VBA editor window, situated on the tool bar. You can also test by pressing F5.
When tested, the form should pop up. Type "TEST" in to the Name field and then press Submit. Check back on the Excel document and the database should now be populated with "Name" in the second row.
Return to the editor and finish populating the rest of the database by updating the "With" section of the code to the following:
'The below line tells VBA we're only dealing 'with Sheet1 With Worksheets("Sheet1") 'Cells tells VBA that we're looking at 'a cell, the information inside the 'brackets is the row number and then 'the colum number. In this case we're 'using "lastrow" as the row number as 'thats the last row we worked out earlier. 'We then tell the value of the cell to 'be overwritten with the value in the 'Names section of the form .Cells(lastrow, 2).Value = Names.Value .Cells(lastrow, 3).Value = DOB.Value .Cells(lastrow, 4).Value = Gender.Value .Cells(lastrow, 5).Value = email.Value .Cells(lastrow, 6).Value = Telephone.Value .Cells(lastrow, 7).Value = TCs.Value End With
Test the code again and this time populate all fields with information. You'll notice there is an issue with the Gender field, there's nothing in the drop down box! Fill everything else in and you should end up with the following in the database (or something similar depending on what you entered):
This proves that the Form is working as it should, now all we need to do is have a way of opening the Form and sorting out the options in the Gender field.
Populating Drop Down Lists On UserForms
This is actually very easy and uses the same With method used to populate the database. We need to add the Genders Male & Female to the list before the Submit button is pressed, so it cannot be added to the button's code, it must go in a it's own code that is called when the form is Activated.
Below the End Sub line of the code we added earlier, copy the following code:
(If your view in the VBA editor is not on the Code view for the form, right click on Add in the Project Window and click "View Code")
Private Sub UserForm_Activate() End Sub
This code will be run when the UserForm it belongs to is Activated. Above the End Sub, copy the following code:
With Gender .AddItem "Male" .AddItem "Female" End With
It's as simple as that. When the code is called, VBA will now add "Male" & "Female" as options in the drop down. You can add as many items as you link in this fashion, however for long lists there are better ways of doing it which I'll cover in later hubs.
Now we need to create a one line code to open the UserForm when needed
Creating The "Open Form" Code
In the Project Window of the VBA Editor (Top Left), right click on any item and select "Insert" and then "Module" (not Class Module!). A new module will appear called Module1. Double click it and in the Properties Window (Bottom Left), rename it to "Form_Open". In the blank code space to the right, copy the following code:
Public Sub Open_Add() Add.Show 'Show the Form Called "Add" End Sub
Once that is done, head back to the Excel document containing the database and draw a square shape to the right of the database. Right click it and edit the text to read "Add Item", you should now have something that looks similar to this:
Right click the button and select Assign Macro, from the list that appears, choose Open_Add and press OK. Now when you click the button, your form will load, you can populate it and then submit it to your database!
In case you get stuck anywhere, you can download the complete working example from the link below
The best reference to have if your new to VBA