You are reading the article Excel Forms – Insert, Update And Delete updated in September 2023 on the website Speedmintonvn.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Excel Forms – Insert, Update And Delete
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.
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.
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.
Modular Code
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.
Homework
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
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.
Form Code
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
You can download your own copy of this code as a .xlsm
You're reading Excel Forms – Insert, Update And Delete
Update the detailed information about Excel Forms – Insert, Update And Delete on the Speedmintonvn.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!