Carrying on with the Excel form I created in an earlier post, I’m adding code to allow us to insert new records into any row of the table (not just at the bottom of the table), delete records and update existing records.
Related Posts: This is the last part of a series of posts I've written about Excel Forms. The other posts might interest you.
Excel Forms Create an Excel user form
Excel Date Picker (Calendar) Use a VBA class to implement a date picker. Sample code and examples provided. A VBA class is better than an ActiveX control in that it doesn't require anyone who uses your workbook to install additional controls or software.
Excel Form Data Validation Check the data being entered in your form is valid, and inform the user about errors
Searching for Data With a User Form. Create a userform to search for data on the worksheet.
We’ve already done most of the hard work designing the form and writing the VBA which controls and validates data entry. We now need to add a few new buttons and a little code.
I’ve also added a spin button control to allow us to navigate up and down through the table. Beside this is a little display showing us which record we currently have selected, and the total number of records.
As you might expect, the new buttons do exactly what they say. I’ve changed the ‘Enter Expenses’ button to now read ‘Add’ so it’s function is distinct from the other buttons.
Form data validation is exactly the same as before and is used when adding, inserting or updating data.
When you open the form, the last record in the table is loaded into the form fields. If there are no records, the form is empty.
To add a new record, enter your data and click on ‘Add’. As you add records, you’ll see that the display in the top right of the form changes to show the total number of records in the table, and the currently selected record. New records are added to the bottom of the table.
You can use the up and down buttons on the spin control to move up and down through the table. As you do so, the data for the current record is loaded into the form, the record is selected (highlighted) on the worksheet, and the number of the current record is shown at the top right of the form.
To update an existing record just change the data in the form and click on ‘Update’.
If you want to delete a record, navigate to it and then click ‘Delete’.
As more functionality was added to the form, it became necessary, that is, it became more efficient, to take parts of the code and place that code into a sub where it could be called as required.
One example of this is the PopulateForm() sub. This is called when the form is first displayed (initialized), and as you navigate up and down through the records. Rather than write exactly the same code twice, the PopulateForm() sub is called to load the current record’s data into the form.
Likewise with the information displayed at the top right of the form to show the current record and the total number of records in the table. The sub UpdateRecordDisplay() is called to change this every time a record is added, deleted, or we move up and down through the table.
After my earlier posts on forms a lot of people asked me to add the ability to insert, update and delete records. Now that is done, what other enhancements do you think would be good in the form?
Some ones that spring to my mind are:
- Navigating up/down x records at a time, rather than one by one
- Seek to a specific record
- Searching for data
- Filtering/sorting the table
- Protecting the sheet to prevent data being changed, forcing data entry via the form only.
Let me know your thoughts and why not have a go at coding some of these enhancements yourself? I’d love to see your results.
Enter your email address below to download the sample workbook.
You can download your own copy of this code as a .xlsm