+ Reply to Thread
Results 1 to 6 of 6

Fix Needed for Code to Send Correct Data to Correct Sheets

  1. #1
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    843

    Question Fix Needed for Code to Send Correct Data to Correct Sheets

    Hello and a very happy new year to you all.

    I have a workbook containing three sheets. It's purpose is to allow users to add or make changes to a list of items, display the current item information, and keep a record of previous item data recorded. The first sheet (Entry Page) is for adding items/making item changes, the second (Current List) is for displaying current item information, and the third sheet (Historical List) is for keeping record of all the item entries ever made. A copy of the workbook is attached.

    I am currently trying to perfect the code I have so far (thanks to the amazing help on this forum). It is partially doing what I want it to, and partially not - I'll explain:

    The workbook opens to the Entry Page. The first value to enter is the item Number - Cell A2.

    Here's the way it should work:

    If the number entered is a new number (a number that is not yet listed on the Current List), the user would continue adding the rest of the information, then hit the Submit button - and the information would be entered in numerical order into the Historical List, displayed on the Current List, and the data on the Entry Page cleared to make ready for any further additions/changes. If the item number entered is already in the Current List, all the current information (Cells B3 through B10) of that item number would be displayed for the user to view. If the user changed the information and hit the Submit button, the updated information would be added to the Historical List, and the Current List would then display the latest update. And the Entry Page would be blank and ready once more for the next entry.

    Here's where I'm at with my code:

    1. When I submit a new item number and it's information and click Submit, the entry page data gets cleared like it should, and it all gets added perfectly to the Current List, but not to the Historical List. I think the code should be set up so that the data from the Data Entry page would get added to the Historical List first, and the Current List would be set up to show only the most current information from there.

    2. When I change a number already in the Current List and hit Submit, the entry page data gets cleared like it should, and the information gets added to the Current List; but on the Historical List, another copy of the previous entry for that item gets added instead. Not sure what's wrong with the code there.

    3. I have a message box come up right after entering a current item number asking whether I want to edit the information - I'd like to get rid of it altogether, but I'm not sure how to do that without messing up the part of the code that works.

    Any suggestions?
    Attached Files Attached Files
    Last edited by swordswinger710; 01-04-2012 at 11:22 AM.

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

    Re: Fix Needed for Code to Send Correct Data to Correct Sheets

    Why have two lists?

    This code will be faster doing the actual copying

    Please Login or Register  to view this content.
    I'm not sure why you are using the worksheet event
    Hope that helps.

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

    Free DataBaseForm example

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

    Re: Fix Needed for Code to Send Correct Data to Correct Sheets

    Why have two lists?

    This code will be faster doing the actual copying

    Please Login or Register  to view this content.
    I'm not sure why you are using the worksheet event

  4. #4
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    843

    Re: Fix Needed for Code to Send Correct Data to Correct Sheets

    Thank you RoyUK; where do I paste your code into mine - and are you saying I can delete the entire worksheet event?

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

    Re: Fix Needed for Code to Send Correct Data to Correct Sheets

    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste
    To run the Excel VBA code:

    Choose Tools | Macro | Macros
    Select a macro in the list, and click the Run button

    Or add a button to the sheet

  6. #6
    Forum Contributor swordswinger710's Avatar
    Join Date
    02-20-2008
    Location
    Bright, Canada
    MS-Off Ver
    2010
    Posts
    843

    Re: Fix Needed for Code to Send Correct Data to Correct Sheets

    Hmm.. that gives me a run-time error 1004. What does your code actually do? The code I have currently, I got to with the help I received here.

    And when you asked about having two lists; that's because I need to have a record of old data, plus a record of only current data. I couldn't think of any other way to do that than to have two separate lists. Unless of course there is a better solution, then I am definitely all ears!

+ 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