+ Reply to Thread
Results 1 to 6 of 6

Store and Retrieve User Inputs from Data Form

  1. #1
    Registered User
    Join Date
    04-02-2010
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    62

    Store and Retrieve User Inputs from Data Form

    Hi All,

    I have an Excel file created by a previous employee and it appears they didn't have a chance to finish the file. I may not be able to post a sample of the file as it contains confidential information but I will try to explain the situation to the best of my abilities.

    Currently, on opening the file the user is prompted to fill a user form for a list of items and the year they choose to fill out. The user form contains a drop down list to select the year and text boxes for the user to input data in. Upon completing the list, I would like the data to be stored in a specific spreadsheet dependent on the Year selected. Therefore, the user can input data for multiple years and have a graph to create a YoY comparison. Also, if the user chooses to change data in a previous year they do not have to refill the entire list out.

    To give you an example: User selects 2010 and fills out 10 apples and 5 oranges. Then selects 2009, 6 apples and 2 oranges. Now if the user goes back to 2010 it should auto populate the 10 apples and 5 oranges and allow the user to overwrite if necessary.

    Thanks in advance for your help.
    Last edited by savio21; 11-29-2011 at 01:50 PM.

  2. #2
    Registered User
    Join Date
    04-02-2010
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: Store and Retrieve User Inputs from Data Form

    My current thought process was to have a button at the bottom of the list in which the user will click when they have completed filling the data for the year. Once they click the button, it will look for the Year selected, find the spreadsheet with the name matching the Year and paste the data into the spreadsheet. Now if the user selects that year again, it will look up the spreadsheet first and auto-populate the list. Additionally, I will be creating spreadsheets for each year 2007 to 2020.

    I hope that makes sense.

    Thanks again

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Store and Retrieve User Inputs from Data Form

    It's not a good design to have different sheets for each year. Much better to have one data sheet with a column for the year. This can then be used to create a summary sheet
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    04-02-2010
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: Store and Retrieve User Inputs from Data Form

    Thanks for your response Roy. I think I can easily convert the multiple sheets to a single summary sheet as it appears the formulas are referring to the same values in each sheet. However, I'm still stuck on linking the textboxes in the form to the specific cells so users don't have to re-enter the same data over and over again.

  5. #5
    Registered User
    Join Date
    04-02-2010
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: Store and Retrieve User Inputs from Data Form

    I think I've figured it out but I was wondering if you guys could help me in cleaning up the code. I have created a sample workbook that does what I need, but i would like to improve the look/efficiency of the code in the update button and the Combo box change:

    Command Button Update
    Please Login or Register  to view this content.
    Combo Box
    Please Login or Register  to view this content.
    As you can see the code is a little long as it is and only has 3 drop down options and 3 text boxes (my original file has 20 drop down options and about 44 text boxes!!!). I was hoping someone could help me almost write a VLOOKUP or Index statement to check the year in the combobox and copy all the text into the corresponding column.

    Thanks again for your help!
    Attached Files Attached Files
    Last edited by savio21; 11-30-2011 at 11:03 AM.

  6. #6
    Registered User
    Join Date
    04-02-2010
    Location
    Toronto
    MS-Off Ver
    Excel 2007
    Posts
    62

    Re: Store and Retrieve User Inputs from Data Form

    Bump! Any help would be greatly appreciated, my code appears to be getting out of hand.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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