• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Forms – Insert, Update and Delete

You are here: Home / Excel VBA / Excel Forms – Insert, Update and Delete
Excel forms, insert, update, delete data
August 21, 2015 by Philip Treacy

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.

excel forms insert update delete new form layout

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.

excel forms insert update delete record counter

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.

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

Excel forms, insert, update, delete data

More Excel VBA Posts

automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA

Static Variables in VBA

Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image

Save Chart as Image

List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists

Clear Downstream Dependent Data Validation Lists

Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar

Excel Status Bar

Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA

Excel Progress Bar for VBA

Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba

Error Handling in VBA

Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject

Finding File Meta Data Using FileSystemObject

Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points

More Userforms Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Hide, Delete, Sort Sheets with VBA in Excel

Hide, Delete and Sort Sheets With VBA Userform

Using a form and some VBA, you can quickly hide, unhide, delete and sort sheets in your Excel workbooks.
add data to combo box drop down list in excel form

Add Data to Combo Box Drop Down List in Excel Form

If the values in your Excel Form's drop down list aren't sufficient, allow the user to add their own, and save the new value in the list to use again.
excel form data validation

Excel Form Data Validation

Thorough and effective Excel form data validation techniques. Clearly communicate errors to your users without annoying them.
excel date picker

Excel Date Picker

An Excel date picker (calendar) for all versions of Excel, both 32 and 64 bit. An easy to use VBA class, with examples to download in a workbook
excel userforms

Excel Forms

Learn how to create Excel forms for tasks like data entry. Use form controls to gather data, then enter this data into a table on your worksheet.
Excel Form Controls

Excel Form Controls

Add interactivity to your spreadsheets with user friendly form controls like option buttons, combo boxes, check boxes and scrollbars.

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Excel VBATag: Excel VBA, Userforms
Previous Post:Excel Fill Series ToolExcel Fill Series
Next Post:Excel Flash FillExcel Flash Fill

Reader Interactions

Comments

  1. ANAND

    January 31, 2020 at 5:30 pm

    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

    Reply
    • Philip Treacy

      January 31, 2020 at 5:52 pm

      Hi,

      You can post it in a topic on the forum.

      Regards

      Phil

      Reply
  2. Tom

    November 19, 2019 at 12:58 am

    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?

    Reply
    • Catalin Bombea

      November 19, 2019 at 11:58 pm

      All you have to do is to unprotect the sheet before writing data.
      sheet.Unprotect Pass
      write data
      sheet.Protect Pass

      Reply
      • Tom

        December 6, 2019 at 10:31 pm

        Thanks Catalin! I was able to make it work using the information you provided; much appreciated.

        Reply
  3. Jim Johnson

    October 19, 2019 at 10:07 pm

    What code would you need to change from a spinner to a combo box?

    Reply
    • Catalin Bombea

      October 20, 2019 at 2:03 pm

      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

      Reply
  4. Stafford Johnson

    July 31, 2019 at 3:40 am

    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?

    Reply
    • Philip Treacy

      July 31, 2019 at 11:54 am

      Hi Stafford,

      It’s Set in the UserForm_Initialize() sub.

      Regards

      Phil

      Reply
  5. m meier

    July 28, 2019 at 1:09 am

    is there a way to add a search field for the records ?

    Reply
    • Catalin Bombea

      July 29, 2019 at 1:10 pm

      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.

      Reply
  6. Tom

    July 26, 2019 at 1:08 am

    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!

    Reply
  7. joni

    April 29, 2018 at 11:55 pm

    how to change year 2015 to 2018

    Reply
    • Philip Treacy

      May 1, 2018 at 2:45 pm

      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

      Reply
  8. Mynda Treacy

    March 14, 2018 at 9:57 am

    Hi Sunil,

    The download link is in the post above under the heading ‘Form Code’.

    Mynda

    Reply
  9. Heri

    March 1, 2018 at 1:49 pm

    Thanks,it’s my solution

    Reply
  10. Sarah

    June 1, 2017 at 7:09 pm

    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

    Reply
    • Philip Treacy

      June 1, 2017 at 7:20 pm

      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

      Reply
  11. Rajabhau

    May 10, 2017 at 5:07 am

    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

    Reply
    • Catalin Bombea

      May 10, 2017 at 1:48 pm

      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

      Reply
  12. shexcel

    March 31, 2017 at 8:35 am

    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?

    Reply
    • Catalin Bombea

      March 31, 2017 at 5:18 pm

      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

      Reply
  13. MANIKANT KUMAR

    February 25, 2017 at 12:42 pm

    Dear Sir,

    Please provide coding of spin button for up and down in user form.

    Regards
    Mani Singh

    Reply
    • Catalin Bombea

      February 27, 2017 at 4:19 pm

      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

      Reply
      • Rajabhau

        May 14, 2017 at 4:39 pm

        Thanks Catalin
        Now everithlg is work fine

        Reply
  14. black

    December 7, 2016 at 2:36 pm

    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

    Reply
    • Catalin Bombea

      December 8, 2016 at 2:08 pm

      Hi ,
      You can try this Search Form.
      Catalin

      Reply
  15. C PIKOULIS

    November 26, 2016 at 12:51 am

    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

    Reply
    • Philip Treacy

      November 26, 2016 at 8:53 pm

      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

      Reply
  16. dr frank

    September 30, 2016 at 3:04 am

    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.

    Reply
    • Philip Treacy

      October 5, 2016 at 2:24 pm

      Sounds good Frank, hope you get those enhancements working.

      Regards

      Phil

      Reply
  17. PS-C21H

    August 24, 2016 at 1:48 am

    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!

    Reply
    • Catalin Bombea

      August 25, 2016 at 4:45 am

      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

      Reply
      • PS-C21H

        August 26, 2016 at 7:49 am

        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!

        Reply
  18. Sonaal

    June 30, 2016 at 4:37 pm

    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 Sonal.jain09@gmail.com

    Thanks in advance

    Sonaal

    Reply
    • Catalin Bombea

      June 30, 2016 at 8:58 pm

      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

      Reply
  19. Luis

    June 21, 2016 at 1:54 am

    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.

    Reply
    • Philip Treacy

      June 21, 2016 at 9:56 am

      Hi Luis,

      If you open a Helpdesk ticket and attach the workbook, I’ll take a look at this for you.

      Regards

      Phil

      Reply
  20. Micko

    February 6, 2016 at 6:56 pm

    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

    Reply
    • Catalin Bombea

      February 7, 2016 at 3:29 am

      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

      Reply
      • Luis

        February 15, 2016 at 5:09 am

        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

        Reply
        • Catalin Bombea

          February 15, 2016 at 3:27 pm

          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

          Reply
          • Luis Gonzalez

            February 15, 2016 at 3:40 pm

            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

          • Catalin Bombea

            February 15, 2016 at 7:13 pm

            You’re welcome Luis, glad to hear you managed to make it work 🙂
            Catalin

  21. CraigsWortld

    October 31, 2015 at 9:02 am

    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)

    Reply
    • Catalin Bombea

      November 1, 2015 at 5:05 pm

      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 Then
      This will stop users from typing data, and you have a way to check controls to see if they are calculated fields.
      Cheers,
      Catalin

      Reply
  22. james

    October 6, 2015 at 9:10 pm

    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

    Reply
  23. james

    September 24, 2015 at 11:53 pm

    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

    Reply
    • Catalin Bombea

      September 25, 2015 at 2:23 am

      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

      Reply
      • james

        September 25, 2015 at 5:48 pm

        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

        Reply
        • Philip Treacy

          September 28, 2015 at 9:05 pm

          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

          Reply
          • james

            October 6, 2015 at 7:42 pm

            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

  24. james

    September 17, 2015 at 1:18 am

    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

    Reply
    • Catalin Bombea

      September 18, 2015 at 1:44 pm

      Hi James,
      Glad to hear you figured it out 🙂
      Stay tuned, there will be many interesting things to come.
      Cheers,
      Catalin

      Reply
      • james

        September 18, 2015 at 9:27 pm

        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

        Reply
        • Catalin Bombea

          September 19, 2015 at 11:20 pm

          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

          Reply
  25. Bob Craven

    September 6, 2015 at 1:21 am

    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?

    Reply
    • Philip Treacy

      September 8, 2015 at 11:07 am

      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

      Reply
  26. David Wagenius

    August 24, 2015 at 3:11 pm

    Philip
    It would also be nice to be able to print. Be it daily, weekly, monthly etc.

    Reply
    • Philip Treacy

      August 25, 2015 at 9:50 am

      Hi David,

      Do you mean to print records for a set period e.g. a specific date, week or month ?

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.