+ Reply to Thread
Results 1 to 5 of 5

How to repeat fields based on user input in a Data Entry formi?

  1. #1
    Registered User
    Join Date
    12-03-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    How to repeat fields based on user input in a Data Entry formi?

    Hi,

    I have a data entry form in excel where I am collecting parent information. I would like to ask the parents the question "How many children do you have?" and collect child gender, child name and child age. I want to repeat the child gender, child name and child age fields several times based on the input by the user.

    Is there a way this can be done?

    Thanks,

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: How to repeat fields based on user input in a Data Entry formi?

    It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    12-03-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: How to repeat fields based on user input in a Data Entry formi?

    Sample file attached. Based on the number inputted for number of childs, I want the child name, child age and child gender fields to be repeated. If the input is 0 then the child fields should not be shown and instead the user should process to the next question such as address.

    Your help is appreciated.

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: How to repeat fields based on user input in a Data Entry formi?

    Try the attached file. Please note that I had to unmerge your merged cells. You should avoid using merged cells at all cost because they almost always create problems for macros. If you enter 0 in I8, the Child fields will be locked so that no data can be entered in those cells. Also, in order for the macro to work properly, the sheet had to be protected. I protected it without a password. If you want a password, please let me know and I'll modify the macro. The macro is in the worksheet code module.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-03-2020
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: How to repeat fields based on user input in a Data Entry formi?

    Hi,

    Thank you for the solution. I have been playing around with this on the weekend and I was not able to to save data as I kept on getting a object defined error. However the fields were getting repeated based on the input number and you have the idea of what I am trying to achieve. I have reconstructed the entire spreadsheet and have gone with your advice of making sure that fields are not merged and that the spreadsheet is not protected.

    Form Page
    What I want to do is when "Number of Children Under 18 Years of Age?" is 0, the child name, child gender, child date of birth and child membership number should be either greyed out or hidden to the user.

    If 1 or more is entered in the "Number of Children Under 18 Years of Age?" field, the child name, child gender, child date of birth and child membership fields should be repeated in the data entry form based on the user input.

    The field names should also update and say child 1 name, child 1 gender, child 1 date of birth and child 1 membership. For the second child, the fields should say child 2 name, child 2 gender, child 2 date of birth and child 2 membership and so on.

    Database
    When I click save on the form, the information is saved on the Database page. The columns should automatically update and shift across if I have more than 2 childs. For the second child, child 2 name, child 2 gender, child 2 date of birth and child 2 membership columns should automatically be populated and the rest of the columns on the right should shift across.

    When I click on modify button on the form I should also be able to modify all the childs which I have inputted.

    Your Help is appreciated.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. where do I add code for hiding/unhiding fields based on a user input value
    By hdean in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2020, 06:52 PM
  2. [SOLVED] Filter data based on input box entry
    By Grilleman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-12-2014, 09:29 AM
  3. Data Input Form with number of line items based on user input
    By j_gideon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2013, 02:54 PM
  4. [SOLVED] User input, then repeat macro.
    By The Skipper in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-03-2013, 01:27 PM
  5. Replies: 8
    Last Post: 08-18-2008, 07:29 PM
  6. Repeat macro from user input
    By traxwriter in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-14-2008, 03:31 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1