In a previous post we have seen how to create a simple registration system in Excel using ActiveX controls and VBA macros. In this post we see how to input the data through a Userform. Excel Userforms are very powerful and allow adding more control to the data entry process and the interaction with users. In this example we create a Userform to register employees into the system. The records are saved to the active worksheet, but could also be saved to a different file, or a DB. When using the same workbook, we may want to protect the workbook structure and/or worksheet altogether – see more about workbook protection here: Protect Selected Workbooks Excel VBA Macro
Registration Form
The registration form in this example is a regular Userform associated with certain macros to control user input and to process the response. This post assumes you already have some basic knowledge about Excel Userforms, otherwise, you can learn more about it in this other page: Training for beginners: Userforms. First we need to add the Userform to the VBA project in the same way we add a standard module. Let’s change the default name of the Userform to “RegForm” for example.
We can drag the controls from the tool box into the Userform to create the layout of the registration form. The design is similar to the one we saw in this previous post: Simple Registration System in Excel with Form Controls. Thus, we will have some labels, text boxes, combo boxes, and two or three command buttons. We are breaking down the date field into three combo boxes, let’s call them comboDay, comboMonth, and comboYear. Similarly, we change the name of the text boxes used to input the name, role, department, and manager (tbName, tbRole, tbDepartment, tbManager). We can also change the name of the command buttons (cbSend and cbCancel).
The Userform needs to be initialized to populate the drop-down list of the combo boxes, and add any other predefined values such as the current date, for example. We double click anywhere in the Userform to access the code window and add the Initialize event procedure, which triggers when opening the form in Excel (see later). Then we can populate the combo boxes as follows. The line of code below populates the list of months from the corresponding custom list available in Excel.
comboMonth.List = Application.GetCustomListContents(4)
We can use a simple loop to populate the list of years too. In this case, we use the AddItem method as indicated below (that populates the box with years from 2020 to 2040.
Once we know the current month and year, we can get the number of days in that month with the DateSerial function, and add the list as done in the previous post – see here for more details about that.
Show the form
We may want to add a button to the main worksheet to open the registration form. This could also be triggered when opening the workbook if we just want users to fill in their information right away. Regardless of the trigger, we need to call a public procedure to open the Userform. We usually add such procedure to a standard module with the following line of code to open the Userform.
This one line of code is sufficient to open the Userform. Note that using "Sub" alone is equivalent to "Public Sub", and therefore the procedure is public; we could not call the procedure from other modules or location if it was private.
We can also use that procedure to set some properties of the Userform in advance such as the caption, certain labels, or predefined content in a text box or combo box. Actually, that’s an alternative to using the ‘Initialize’ event that we have seen earlier. Instead of doing that, we could pre-populate certain fields in the Userform via this procedure.
Submit and Cancel
We double click a control to add the macros or procedures associated with that particular control. In the code window, we select the associated event from the drop-down list on the right. In the case of the command buttons, that’s the ‘Click’ event.
When submitting the form, we need to read the information in the different fields of the Userform. We assign that info to the corresponding variables, and then write those values to the worksheet, or to other file, or a DB.
In this simple example we just write the values to the active worksheet (the registry). First we need to get the last row with data in that sheet (lr), so that the entries are added to the first available row. We need to add the following code to the Click procedure of the ‘Submit’ button.
Finally, we need to close the form. We do that with the following line of code, and that’s the only line of code we will add to the Click procedure of the ‘Cancel’ button.
Unload Me
We have seen several ways to create a simple registration system in Excel using VBA macros. But we may need to gather such info online. In that case, we can use Microsoft Forms or Google Forms to request data and populate an online Excel file or Google Sheet. Then we can import such data to our local Excel file to work with it if needed. We will see how to do all that in the next post.
Click Here to download Excel Simple Registration with Userform.
No comments:
Post a Comment