My Online Training Hub

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

Excel Forms – Insert, Update and Delete

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

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.
Excel forms, insert, update, delete data

More Excel VBA Posts

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
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
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
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
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
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
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
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
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 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
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
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
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
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
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
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
Thorough and effective Excel form data validation techniques. Clearly communicate errors to your users without annoying them.
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
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
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
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
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
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
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
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
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
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
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
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
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Tag: ,

Reader Interactions

Comments

  1. ANAND

    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

  2. Tom

    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

      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

    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

    • Catalin Bombea

      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

    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

    • Philip Treacy

      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

  10. Sarah

    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

  11. Rajabhau

    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

      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

    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

      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

    • Catalin Bombea

      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

  14. black

    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

  15. C PIKOULIS

    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

  16. dr frank

    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

  17. PS-C21H

    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

      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

        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

    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

      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

    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

      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

    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

      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

        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

          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

            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

  21. CraigsWortld

    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

      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

    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

    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

      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

        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

          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

            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

    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

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

      Reply

      • james

        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

  25. Bob Craven

    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

      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

Leave a Reply

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

Current ye@r *