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’.
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
- 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.
Form Code
Enter your email address below to download the sample workbook.
You can download your own copy of this code as a .xlsm
I have written a code to search, Add, Update and delete the records in a English to Hindi dictionary.
I want your opinion on the code. I want to know is there any simple version?
How can I send the excel file to you
Hi,
You can post it in a topic on the forum.
Regards
Phil
I’ve been able to use this excellent information to develop an input form for data which is then displayed on a dashboard. The problem I’m currently trying is as follows.
I need for the user to be able to input an optional hyperlink in the form and have not been able to find a way to allow it. While I originally had the data table in a separate hidden worksheet, I made it visible and unlocked the cell column to allow the user to directly enter the link. I then protected the rest of the worksheet to reduce the risk of accidental data corruption.
As often happens, the law of unintended consequences came into play. With the table now protected, I cannot “Add” data using the input form. Apparently there is a known issue with Excel which in essence disables the addition of new rows in a protected table.
Can you possibly tell me how to get the input form to accept hyperlinks or how, using the input form, to add data to a protected table?
All you have to do is to unprotect the sheet before writing data.
sheet.Unprotect Pass
write data
sheet.Protect Pass
Thanks Catalin! I was able to make it work using the information you provided; much appreciated.
What code would you need to change from a spinner to a combo box?
Hi Jim,
I guess you want to use one of the table fields to populate the combo, and select one of them to display data.
You shoule use something like this:
Private Sub Combobox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(Combobox1.Value) = 0 Then Exit Sub
Dim i As Long
For i = 1 To ExpensesTable.ListRows.Count
If Combobox1.Value = ExpensesTable.ListRows(i).Range.Cells(ExpensesTable.ListColumns("Client Name").Index).Value Then
CurrentRow = i
UpdateRecordDisplay
Exit For
End If
Next i
End Sub
You should also add code to UserForm_Initialize() event to populate the combo with values.
Regards,
Catalin
In looking through the code, I do not see anywhere that you are initializing the ExpensesTable global variable to the data table (using Set command). There is one instance I found,, but it is commented out. How does the code know to use the Expense data table?
Hi Stafford,
It’s Set in the UserForm_Initialize() sub.
Regards
Phil
is there a way to add a search field for the records ?
Hi,
Yes, it’s possible, but there is no standard solution, it needs to be build according to user specific needs.
Can you open a new topic on our forum for this? You can upload there the form you have that needs to be modified.
Just wanted to drop a note thanking you for all of the work the two of you do to help expand everyone’s knowledge and capabilities in using Excel. The detail and clarity of the information you provide has enhanced my abilities far beyond where I would have previously conceived.
You’re the #1 Excel resource for me!
how to change year 2015 to 2018
Hi Joni,
The form uses the date in the last record as its starting date. In the sample workbook this is 30/07/2015. Just change that if you want a different date to start with in the form.
Once the form appears, click on the month and year boxes to change them.
Regards
Phil
Hi Sunil,
The download link is in the post above under the heading ‘Form Code’.
Mynda
Thanks,it’s my solution
Hello, this form is very useful.
If one has a list of staff names and corresponding employee numbers, how would a person have VLOOK up be performed against the selected staff names and have the employee number printed to the spreadsheet in a specific column?
Also, depending on the value in the first combox box, which I have renamed to ‘Team Leader’ how would I have another another V Look up print to the spreadsheet the corresponding department number?…
On another form I created this code worked but I cannot integrate it with yours:
Private Sub SaveData()
‘Save the data from the controls into current row
Dim C As MSForms.Control
Dim varValue As Variant ‘Must be variant to accept different types of data
For Each C In Me.Controls
If C.Tag “” Then
‘Get the value from the control
varValue = C.Value
Select Case C.Tag
Case “E”, “F”
‘These are a direct copy of their values to the worksheet
Worksheets(“Data”).Range(C.Tag & ThisRow.Row) = varValue
End Select
‘Insert Vlookup formula for the Employee Number
Worksheets(“Data”).Range(“E” & ThisRow.Row).FormulaR1C1 _
= “=IFERROR(VLOOKUP(RC[1],dropdown!C2:C3,2,FALSE),””””)”
Worksheets(“Data”).Range(“F” & ThisRow.Row).FormulaR1C1 = _
“=IF(ISNUMBER(SEARCH(“”Matthew””, RC[-2])),””木工””, ” & _
“IF(ISNUMBER(SEARCH(“”Mark””, RC[-2])),””木工””, ” & _
“IF(ISNUMBER(SEARCH(“”Luke””, RC[-2])),””木工””, ” & _
“IF(ISNUMBER(SEARCH(“”John””, RC[-2])),””木工””, ” & _
“IF(ISNUMBER(SEARCH(“”Jesus””, RC[-2])),””木工””, ” & _
“IF(ISNUMBER(SEARCH(“”Obama””, RC[-2])),””木工””, ” & _
“IF(ISNUMBER(SEARCH(“”Trump””, RC[-2])),””木工””, ” & _
“””””)))))))”
End If
Next C
Saved = True
End Sub
Hi Sarah,
Can you please create a post on the forum and include a sample file so we can have a look at this.
Regards
Phil
This workbook is amazing!
I wondered if it would be possible to userform show in ‘input sheet’ and
row edit, add,delete in ‘Data sheet’
Can you please tell me how to change this?
Thank you!
Regards
Rajabhau Tribhuvan
Hi Rajabhau,
You can move the Enter Expenses button in any sheet you want, to call the form.
You will have to make some minor changes to code:
replace ActiveSheet with Sheet1 (or with ThisWorkbook.Worksheets(“Expense Report”) (there are multiple occurences of ActiveSheet, replace them all)
Replace this line:
ExpensesTable.ListRows(ExpensesTable.ListRows.Count).Select
With:
Application.Goto ExpensesTable.ListRows(ExpensesTable.ListRows.Count).Range
Catalin
Hello there, a lot of effort has gone into this tutorial so thank you very much.
I notice that you have defined a cell range, called it ‘expenses’ and made reference to that in the VBA code.
I wondered if it would be possible to choose a particular worksheet and then edit entries on that worksheet once the worksheet has been selected from the combobox? Could you please let me know?
Code for combobox:
Dim ws As Worksheet
Private Sub cbContactType_Change()
With Me.cbContactType
If .ListIndex -1 Then Set ws = Worksheets(.Text)
End With
End Sub
I’m not sure how I would go about modifying this code to make it work?:
If CheckForErrors > 0 Or ExpensesTable.ListRows.Count < 1 Then Exit Sub
ModifyTableRow ExpensesTable.ListRows(CurrentRow).Range
The data is always in cells A to G.
Also, can I use command buttons instead of a spinbutton/is there a way to make a spin button display horizontally instead of vertically?
Hi ,
The spin button has the .Orientation property, which can be set to 0 (fmOrientationVertical) or to 1 (fmOrientationHorizontal), from VB editor, spinbutton properties panel.
Note that the default value for this property is fmOrientationAuto, this means that depending on the way you format the box with and length, it will automatically switch from vertical to horizontal. Try dragging the corners of the spinbutton, to make the width higher than the height, and you will see that it will switch to horizontal.
Not sure what are you trying to do with multiple sheets. If you change the sheet, you have to change the name of the listobject too.
Set ws = Worksheets(.Text)
Set Tbl=ws.ListObjects(“Table2”) (or Set Tbl=ws.ListObjects(1) if you have only one table in each sheet)
If you need more help, you have to upload a sample file on our forum (create a new topic), with details of what you want to do.
Catalin
Dear Sir,
Please provide coding of spin button for up and down in user form.
Regards
Mani Singh
Hi Mani,
There is no universal code for SpinButtons.
There are many events that can be used for Spin Buttons (SpinButton1_Enter, SpinButton1_Exit, SpinButton1_Change, and so on) , you have to write the codes for what should happen when a certain operation is done:
Private Sub SpinButton1_SpinDown()MsgBox "Down button was pressed."
End Sub
Private Sub SpinButton1_SpinUp()
MsgBox "Up button was pressed."
End Sub
Catalin
Thanks Catalin
Now everithlg is work fine
hai, great job there author.
it just that can you help me with searching for data and filtering it based on the search fields.? im doing an internship project which required me to input a certain data and display it on a list box
Hi ,
You can try this Search Form.
Catalin
great job
Thank you for all the knowledge you share with us.
PS
sorry but I have a question how to add text to the end of all worksheets in α workbook with vba
Thanks.
If you have a qs about something you can post it on the forum and we’ll see what we can do to help.
Phil
This workbook is amazing! – I am working on some code that would delete the data that was entered. Basically, a reset button.
The second item I am working on is to ‘lock’ / protect the fields from entry or modification except through the form/reset button.
Sounds good Frank, hope you get those enhancements working.
Regards
Phil
Philip,
This is a great example and very helpful for people like myself who are trying to learn the basics of userforms. However, in my form, I want the userform to populate from the selected row and not the last row of the table. I have tried everything I can think of and looked everywhere I could for the way to load the current row into the userform. However, it is the simple things that confound most often! Can you please tell me how to change this?
Thank you!
Hi Pamela,
You have to replace the UserForm_Initialize code with this one:
Private Sub UserForm_Initialize()
Set ExpensesTable = ActiveSheet.ListObjects("Expenses")
If Calendar1 Is Nothing Then
Set Calendar1 = New cCalendar
With Calendar1
.Add_Calendar_into_Frame Me.CalendarFrame
.UseDefaultBackColors = False
.DayLength = 3
.MonthLength = mlENShort
.Height = 142
.Width = 180
.GridFont.Size = 7
.DayFont.Size = 7
.Refresh
End With
End If
'Initialise for empty table
ChangeRecord.Min = 0
ChangeRecord.Max = 0
CurrentRow = ExpensesTable.ListRows.Count
If Not ExpensesTable.DataBodyRange Is Nothing Then
If Not Intersect(ActiveCell.EntireRow, ExpensesTable.DataBodyRange) Is Nothing Then
CurrentRow = ActiveCell.Row - ExpensesTable.HeaderRowRange.Row
End If
End If
If CurrentRow > 0 Then
ChangeRecord.Min = 1
ChangeRecord.Max = ExpensesTable.ListRows.Count
'Load last record into form
PopulateForm ExpensesTable.ListRows(CurrentRow).Range
ExpensesTable.ListRows(CurrentRow).Range.Select
UpdatePositionCaption
Else
RecordPosition.Caption = "0 of 0"
End If
End Sub
Let us know if it works.
Cheers,
Catalin
Hi Catalin,
Yes, this did work. I don’t really understand how it works … still trying to figure it all out, but it did work. Thank you for helping me out!
Hi! First off, many thanks for this great example! When I try to add a new record though.but i can not understand how to use data validation in total row its great when you click sum its sum when you click average its average wow how to do this please guide me my e mail id is [email protected]
Thanks in advance
Sonaal
Hi Sonaal,
How can we help you, can you be more specific? If you right click the table, and click on Totals Row, the totals row is displayed, and you can choose from those dropdowns the aggregation you want: sum, count, average. What are you trying to do?
Catalin
Hi I have been using this form since early this year, I have done some modifications including adding a 2nd calendar to the form to use it how I need it, so far it works flawlessly on office 2007 but I just tried the form on office 2010 and the second calendar added by me it’s not supported on office 2010 so I tried to duplicate the calendar from your form and I tried adjusting the codes to make the duplicate calendar work but I haven’t been successful, if it is possible to duplicate the calendar would you mind helping me with the code adjustments?
Thanks in advance
Luis.
Hi Luis,
If you open a Helpdesk ticket and attach the workbook, I’ll take a look at this for you.
Regards
Phil
Hi! First off, many thanks for this great example! When I try to add a new record though, I get the error message that project or library cannot be found with a reference to the calendar. Do you know why this might be happening?
All the best,
Micko
Hi Micko,
You should go to VB Editor, from Tools menu , select References, and check for missing refererences. If it’s the Calendar Control missing, and if you don’t have excel 2010 64-bit version, you can download the calendar control from here: MSCAL.zip
For other useful information, please check this article: Excel-Forms
You will also find there a link to a vba date picker, which is not depending on any library, and it will work on any computer.
Catalin
Hi this form has more features than what I was looking for, I will figure out a way to make use of all the features, it is nicely done, thank you for sharing it, I downloaded the form code and tested the form it didn’t work then I downloaded the calendar control mscal.zip then followed the instructions given to Micko on February 7th since my problem appears to be similar as his but the problem was not solved, only the commands delete and close are working normal, the other commands add, insert, update and clear are not working they trigger this error: (Compile error: can’t find project or library) I am sorry it might be an easy fix but I am not developer just an average guy playing with excel.
Using Office 2007 32bits on windows 10 64bits with mscal.ocx file installed in C:\Windows\SysWOW64
Thanks
Luis
Hi Luis,
Did you register the library, as was described in Instalation instructions found in MSCAL.zip archive? You have instructions there for win 7, Vista systems, writtem by Graham Mayor.
After you register the library, go to Visual Basic Editor, and from menu, choose Tools-References. In the list of library references, uncheck the Missing reference, and scroll the list to find and select the library you registered in previous steps.
Let me know if this solves the problem.
Cheers,
Catalin
Oh wow I had done it all except for the uncheck missing references, I just did it and it’s working beautiful, thank you Catalin for your reply. Thanks for sharing your great work.
Cheers
Luis
You’re welcome Luis, glad to hear you managed to make it work 🙂
Catalin
First off… love the course and I enjoy these helpful code tidbits.
I have made great use of this expense record workbook and have adapted it to my specific requirements.
I have a question though, how would I modify the CheckForErrors code to ignore specific textboxes/Comboboxes, possibly using the control tag method. Like you did with the “Optional” tag. What I have is locked textboxes that are calculated based on other entries (I might add that I add my own keystroke check to only allow certain entries prior to these calculations). The CheckForErrors picks up the “%” signs in my calculated formats. I suppose I could check for locked textbox value but this will not help me with comboboxes as they are not locked. Any Idea?
Thanks Craig (CraigsWorld)
Hi Craig,
What do you mean by having locked textboxes? Are they disabled by setting the Enabled property to False?
If you simply have a text box that you don’t want users to type in data, you can set the Enabled property to False for that text box, then check that in code:
If Ctrl.Enabled = True ThenThis will stop users from typing data, and you have a way to check controls to see if they are calculated fields.
Cheers,
Catalin
I have a semi functioning search that works form a command button that stops at the first instance of what your searching for
if/when I work out how to make this move forward/backward i’ll post
(if anyone wants to chip in and see if they can make it forward/backward to the next on the search I wont complain)
Option Explicit
Private DataTable As ListObject
Dim cl As Variant
Private Sub CommandButton1_Click()
Set DataTable = ActiveSheet.ListObjects("Data")
For Each cl In Sheet1.Range(DataTable)
If cl.Value = TextBox3.Value Then
TextBox1.Value = Range(cl.Address)
TextBox2.Value = Range(cl.Address).Offset(0, 1).Value
Exit For
End If
Next cl
End Sub
is there any news/update regarding having a “search” feature.
I’m trying to build a separate form to search through the table (only show what was searched for); but having no luck at the moment
Hi James,
The search form demo I sent you (with spin buttons too) already has this feature, it will display in the form only the rows that contains the search string.
Or is it something else you are trying to achieve?
Cheers,
Catalin
I don’t fully understand the search function in what I’m seeing online (way to complex for me to unpick) and need something that walks me through how to implement a search on a form through a db table.
What has been created by you guys here is brilliant. it explains what needs to be done, why it’s done (in a simple way) and uses a real life situation that makes it easier to relate and understand.
I’ve taken what you guys have done and reworked it into something that I can use within my organization.
I have a form that is used for data entry within the table.
Then I have another form that allows someone to move through the table and edit what is found there; But this is only good for a few entries.
Once the table starts growing to double figures and beyond people will need an easier, and quicker way, to find something with in the db table.
I would like to have a simple textbox/dropdown that when something is entered would search through a specific column.
This would allow you to then move through the edit/search Form; but only moving through the ones that you’ve specified from the search.
I’ve found things on the internet that are either a) to complex b)show me what cell the term I’ve search for is in (but I cant work out how to link this to the spin button)
I was hoping that you may have an another blog entry regarding search through a form, in a step through list manner, that would be coming out soon
Hi James,
Are you able to provide us with the workbook and forms you have already created? The .xlsm you attached to the Helpdesk ticket was 0 KB in size, maybe the upload didn’t complete?
If I can see your workbook it will be easier to help you implement what you are looking for. Unfortunately this kind of thing is quite complex so there’s no real way to make it simple.
I can provide a generic example using my form, but you would have to take that and modify it to work in your environment.
Regards
Phil
if anyone is interested I have a part search working.
it goes through and stops at the first instance of what you are searching for (case sensitive).
need to work on how to step forward/backwards from current location and not restart every time.
set up is
form:
Command button CommandButton1
text box Textbox1
text box Textbox2
text box Textbox3
Option Explicit
Private DataTable As ListObject
Dim cl As Variant
Private Sub CommandButton1_Click()
Set DataTable = ActiveSheet.ListObjects("Data")
For Each cl In Sheet1.Range(DataTable)
If cl.Value = TextBox3.Value Then
TextBox1.Value = Range(cl.Address)
TextBox2.Value = Range(cl.Address).Offset(0, 1).Value
Exit For
End If
Next cl
End Sub
if I get a working solution i’ll post here for everyone
Is there a video that explains the changes and how they work?
I’ve copied the previous form and from that I’ve been able to make something that I can use at work. But I would like to include what you’ve done in the above in what I’ve created
Also work won’t let me install the date picker, so I’ve made a basic date selection using drop downs and a concat
.Cells(1,4) = RasiedDay & “/” & Raisedmth & “/” & RasiedYr
.Cells(1, 5) = Closedday & “/” & closedmth & “/” & closedyr
But I’d like to incorporate an a) isdate so only a valid date option can be entered b)if nothing is entered to just have a blank in its place
I’ve tried declaring them at the start of the sub.
But depending on what I did I either got an error message, debug message, 0/0/0 or 12:00:00 am
If I simply wrap isdate around them I get false (guessing this is because it’s a string and not actually a date)
I’ve also tried to include the option of moving up and down records
This had the effect of inserting rows as I scrolled up and down
Or (if I deleted all the rows from the table) doing nothing at all
What you’ve done is brilliant and any help you can provide would be gratefully appreciated
You guys are a constant source of new things to explore in excel
L.E.:
after writting this i tried again at solving this myself and came up with
Dim ddate As String
ddate = IsDate(RasiedDay & “/” & Raisedmth & “/” & RasiedYr)
If ddate = True Then
ddate = RasiedDay & “/” & Raisedmth & “/” & RasiedYr
Else
ddate = ” ”
End If
which seems to work
Hi James,
Glad to hear you figured it out 🙂
Stay tuned, there will be many interesting things to come.
Cheers,
Catalin
would you be able to give/tell me which sections relate to the spinner control and updating/moving through the records in the user form
I’m interested in understanding this in particular.
but when I try and copy this I cant seem to get it to work (guessing that I’ve not copied over the right subs or I’m missing a declaration someplace)
would be really handy to understand/be able to do this
Hi James,
Please upload your sample file with your codes on our Help Desk (open a new ticket), I will gladly help you.
Cheers,
Catalin
I’m trying to set up a form to enter/edit/delete information in a database of personnel which includes, among other things, last/first names, location, grade level, personnel number, office symbol, etc. I want to be able to call up a record in the user form and revise any information for that record without retyping all the information — for instance, if I can just type in a last name and all the other fields will automatically populate in the user form so I can just change whatever fields need to be changed. In your example it would be like if specific clients are assigned to specific staff and you could just type in the client’s name and the staff name would be automatically loaded into the staff combobox. My concern is that it seems I need to have separate tables for each column so I can name a different table for each combobox. But then I can’t sort the data overall. What would be the best way of doing what I need to do?
Hi Bob,
I’m assuming that you will have multiple records for any given last name, if so which one would you have populated into the form? Maybe another way to approach this is to apply a filter to the table first, then you could use a double click event to open the form using the data in the record you double clicked?
Regards
Phil
Philip
It would also be nice to be able to print. Be it daily, weekly, monthly etc.
Hi David,
Do you mean to print records for a set period e.g. a specific date, week or month ?
Phil