Use Excel VBA to Create an Excel Userform and Manage a Database!

Click here to download the STARTER and COMPLETED files from the video series

In the first ‘in-car’ video, Chris explains how Excel userforms can help with a typical Excel task – managing a database. This can be time-consuming and frustrating and, correctly implemented, Excel userforms can help! We briefly discuss the importance of two functions – adding new entries to a database, and editing existing ones, consider how an Excel userform might help, and highlight the pivotal role of VBA code. Why not download the completed Excel file to ‘get a feel’ for the topic? Then, download the starter file so you are ready to work along with Chris
Why are Excel userforms important and what are some applications?

This video looks at creating a Excel userform in the VBA editor and adding a simple feature or ‘control’. We add a command button and assign some code to the button. In the future, the user will click on this button to transfer data from the userform to the database. We make the command button flash up a message box so that we can understand how the Excel userform, command button, VBA and message box are interacting. This is a simple example, but all userforms follow a similar basic process to get things done.

In this video, we build up a userform to so that it can quickly collect data from the user. We look at three controls, and focus on two ways of gathering data – TextBoxes and ComboBoxes. Textboxes allow the user to make a ‘free text’ entry, whereas ComboBoxes require the user to choose from a menu. ComboBoxes must reference a list in the spreadsheet, and we look at how to get the VBA editor and spreadsheet to interact to achieve this. We also explore how to implement option buttons – another interactive and user-friendly Excel userform feature.

A userform that is not working well can be more of a hindrance than a help! In the fourth video in the series, we look at maximising user-friendliness to make the user experience as smooth as possible. We explore how to configure the TabIndex property in the VBA editor to allow the user to navigate the userform using the Tab key on the keyboard. We also look at how to make default values appear in the controls to speed things up for the user, in addition to some other important details that help create a ‘professional’ feel.

This task involves multiple ‘objects’ – the workbook, the worksheet and the userform, among others. VBA is essential to make these objects interact effectively – it is the glue that binds the various elements together. In this video, we consider how to approach what can appear a complicated coding task. We identify a sensible and simple first step that transfers data from the userform to the spreadsheet, and implement it using VBA. Dealing with the option buttons, however, requires a more sophisticated coding concept, can you guess what?

So far, we have used VBA to move data from the Excel userform to the database. However, we need a way to tell Excel exactly where to put the data in the database. If adding a new entry, we would want the data to appear at the bottom of the database, for example. In this video, we explore how to set up a mechanism to measure the size of the database and to convey this information to the VBA editor. The mechanism is ‘dynamic’ in the sense that it responds to changes in the size of the database. It’s a great example of a common theme in VBA – getting spreadsheet formulae and VBA commands working together to achieve powerful and precise control.

This video is a break from the complex coding! We take a detour through two topics that are often ignored but which are critical if you want to take your spreadsheet to ‘professional’ level. We deal first with the topic of control. We can achieve good levels of control by using validation to check user inputs. In the video, we look at setting up a ‘custom’ validation control to check if a proposed name is already in the spreadsheet, or not. The second topic is user interaction. It is not always clear what has happened when a VBA routine has run and it is a good idea to let the user know. How can we make this happen in the Excel userform task?

The ability to edit (rather than add a new) entry is an important function that presents a new challenge to the programmer. In this video, we consider the task and identify the main steps. We look at how to create a new, simple userform to allow the user to highlight the entry to be edited. We use an offset formula in the spreadsheet to define a ‘dynamic’ range that changes size according to the number of entries in the spreadsheet. Then, we create a mechanism to establish the row that the ‘target’ entry to be edited is on.

In this video, we explore how to extract data from the database and to load it into an Excel userform. Note that our approach is NOT to create two separate userforms – one for data input, and one for editing, which would represent a duplication of effort; rather, we use the original userform and attempt to establish two ‘modes’ – one for new entry input, and the other for editing existing entries. We look at how to create a sophisticated position control mechanism to retrieve data from the database accurately, and to display it in the Excel userform. This sounds like a big job, but ‘recycling’ code that we know already works can help!

In the final video, we explore how to create the sense of ‘mode’ that we refer to above. This is an intricate task that involves ‘saving’ some information for use in subsequent VBA routines. We look at how to combine a worksheet, the Excel userform and the VBA editor to make this happen. The result is a powerful, interactive and robust Excel userform that allows the user to quickly add data to and edit a large database.

Note: Chris did do some ‘tidying up’ of the code after shooting, to improve the quality of the completed file. This involved improving the in-code annotations and the structure of the code. No new coding ideas were introduced, however, and the mechanisms all work in the same way. Don’t feel that you should copy every detail of Chris’ approach, however; you now have the tools to design and implement your own powerful Excel userform-based applications. Good luck!