Comments on: How to make and use Excel data entry form

When it comes to inputting information in Excel spreadsheets, most people do it the traditional way - cell by cell, row by row. To make the process more use-friendly and less time-consuming, you can use a special data entry form. Continue reading →

Table of contents

Comments page 2. Total comments: 53

  1. Excellent and thorough article :-)
    I want to go a bit more deeper into the use case utilizing 'Formulas in data entry forms' you describe.
    I fully understand not to have new/recalculated formulas to occur for entries not being changed by the Data Entry Form.
    So can one rely on this, and making below feasible ?
    ----------------------------------
    Use Case
    I want to have a calculated cell for each row holding the UserID for the user last modifying it. It has obviously to be Calculated, to hinder change by the Form. Its not going to be as a Security Measure, just to ensure that a central team can collate information on who contributed.
    I imagine that a default value is set, also calculated in (a smart way). So any other value that appears in the table column, is there because of someone changed it.

      1. Thanks Alexander
        It might be a circumvention, indeed. I will try this.

        But FYI: I did try to code some Event Triggered code, but these seems not to be triggered when using Forms this way :-(

  2. Is there any VBA code that would display the custom drop down lists already built as valiation, on the form? We have a dozen custom drop down lists in our orders worksheet? Size, style, color, shipping method, etc.
    Thanks

  3. Hi, Your tutorial on built-in form was very helpful. I have few questions regarding form outlay, table presentation which if at all possible to alter (customize).
    Is there any way, procedure to change table outlay from vertical to horizontal i.e; if instead of having a long table have it horizontal.
    Second how to change the background color.

    Regards

  4. How to restrict use of special characters in text using data validation?

  5. it still work on sharepoint to?

  6. how does this form work when working on a sheet shared with other users. will they still need to the save the sheet before adding the entry to avoid overlap?

  7. I can't find this Form tool in the Quick Access Toolbar. I am using Mac/microsoft 365 subscription...and the excel is Beta Channel. Is it possibly the reason that this Form Tool can't be found?

    1. Hi Christine,

      The Form tool is not visible by default, and you need to add it to the Quick Access Toolbar first. Please check the "How to add the Form tool to Excel" section of this tutorial for the detailed instructions.

  8. Hi , is it possible to create a drop down list within the excel form itself? Thank you

    1. Hi Svetlana.
      Thank you for the detailed information.
      I have the same question as Tarmizi Rahman.... I have a dropdown list on one of my columns. When I use the Data Entry Form, I don't get the dropdown list. If I then type in something that is not part of the dropdown list, I get an error message and can retry. This all works, but it would be so much better if the dropdown list was carried through to the form, so that entries can just be selected from the list while in the form.
      Many thanks.
      Rob

  9. Hi! This is so helpful - thank you so much! Is it possible to put a button on one sheet that opens a data entry form for a range in another sheet (in the same workbook)?

    1. Hi Keri,

      Thank you for this good question! Sure, it's possible. To our sample code, just add the following line to activate the target worksheet (Sheet1):
      Worksheets("Sheet1").Activate

      I've added this example to the tutorial for other users to know, and you can find the complete code in this section: Open data entry form with VBA.

  10. Hi,
    Love your site.
    I don't see how this works in the online version. Can you verify this works on office (Microsoft 365)?

    1. Hi Mickey,

      Data entry form is only available in a desktop app, including Excel for Microsoft 365. In Excel Online it is not supported.

  11. Hello. The "form" option does not appear in my Excel for Mac when I try to add it to the QAT as described.

    1. Hello Philip,

      Like all other tutorials on our blog, this one targets Excel for Windows. From this forum thread, it appears that Excel 2019 for Mac does not have the Form feature. But I don't use Mac and cannot state this with certainty.

  12. In the beginning you mentioned this would work with dozens, even hundreds of columns. I keep getting the error that I have too many fields in my form (I have 95 field columns). How can I create a data entry form?

    Thanks,
    Dan

    1. Hello Dan,

      Sorry for misleading you, my bad. After additional research, it appears that a data form is limited to only 32 columns. I have added this limitation to the article.

      A possible workaround (if applicable in your case) might be to insert a couple of blank columns breaking your table into 3 smaller tables. And then, you can create three separate data forms.

  13. Hi. I just found your site and it's great!
    In my case, I have months across the top of my table (Col B is Jan, Col c is Feb, etc.) and Expenses are listed by Row (labor Row 2, materials Row 3, etc). I want to create a form to enter next month's data. How can I use a form to do that?

    Thanks
    David

    1. I was hoping for the same thing - input by column, not by row. Looks like it's not possible unless you have a ghost/helper worksheet to collect the data by row, then formulas/VBA to transpose it to columns. A lot of work for little benefit :( Looks like we need to request transposed input from Excel.

  14. I am trying to use the below comment in my VBA file to place a formula in the column 'AO' Since the holiday lists might vary based on regions, im using if condition to validate the region from column 'M' and then capture the formula in AO column. But getting Run-time error '1004' error while using the below code. Can anyone help to fix this..

    Dim slr As Long, LastRow As Long, i As Long LastRow = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To LastRow

    If Range("M" & i).Value = "Texas" Then Range("AO" & i).Formula = "=MAX(0,NETWORKDAYS(MAX(AO$1,$R(i)),MIN(DATE(YEAR(AO$1),MONTH(AO$1)+1,0),$S(i)),$L$3:$L$12))" end if If Range("M" & i).Value = "Oklahoma" Then Range("AO" & i).Formula = "=MAX(0,NETWORKDAYS(MAX(AO$1,$R(i)),MIN(DATE(YEAR(AO$1),MONTH(AO$1)+1,0),$S(i)),$K$3:$K$12))" end if

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)