ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Excel VBA - Using A UserFrom To Populate A Database

Updated on August 25, 2014

Getting Started

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.

Download Workbook

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

Download Complete Workbook

VBA References

Absolute Beginner's Guide to VBA
Absolute Beginner's Guide to VBA

The best reference to have if your new to VBA

 

How'd You Get On?

Did you give this a go?

See results

Rate This Guide

Cast your vote for this guide

Comments

    0 of 8192 characters used
    Post Comment
    • profile image

      Arran Christie 

      5 years ago from USA

      Good stuff....

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)