+ Reply to Thread
Results 1 to 7 of 7

Button Required to Send Updated/Historical Data to Appropriate Sheets in Workbook

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

    Question Button Required to Send Updated/Historical Data to Appropriate Sheets in Workbook

    Hello!

    I have a workbook which contains 3 sheets. It's purpose is to contain a current and historical record of items and their respective attributes. You will find a sample attached. The layout is basically this: I have an Entry Page (which is for entering new/updated information of items), a Current List (which displays the current information of items), and a Historical List (which displays historical information of items).

    What I'm trying to achieve is to set up a button/something on the Entry Page that would add new/updated information for an item to the Current and Historical Lists.


    I'll explain it in more detail:

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

    If it's a new number (a number that is not yet listed in the Current List), the user would just continue adding the rest of the information, then hit the Submit button - and the information would be entered in numerical order into the Current and Historical Lists, and the data on the Entry Page cleared.

    If it's a number already in the Current List, all the current information on the item would be displayed, and the user would be asked if they want to update the current item's information or just view it. If the user chooses to update, then upon making the changes and submitting, the updated information would be added to the Historical List, and the Current List would now display the latest update. And the Entry Page would be blank once more, for the next entry.


    So! That's the plan - any help/advice is hugely appreciated! Thanks so much in advance!
    Attached Files Attached Files
    Last edited by swordswinger710; 12-17-2011 at 01:36 PM.

  2. #2
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Button Required to Send Updated/Historical Data to Appropriate Sheets in Workbook

    Hi,

    I would suggest that you upload an example workbook with mock data that we can use.
    Please take time to read the forum rules

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

    Re: Button Required to Send Updated/Historical Data to Appropriate Sheets in Workbook

    Thank you so much for your response! I tried to attach the sample workbook to my first post, but had issues doing so for some unknown reason. It's attached now. Thank you!
    Last edited by swordswinger710; 12-17-2011 at 01:39 PM.

  4. #4
    Valued Forum Contributor Steffen Thomsen's Avatar
    Join Date
    10-15-2010
    Location
    Kolding, Denmark
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    953

    Re: Button Required to Send Updated/Historical Data to Appropriate Sheets in Workbook

    Hi,

    Firstly i have removed your merged cells on "Entry Page" as they tend to mess things up
    Try this and see if it fits your needs. I haven't programmed it to take into account formatting, this you can add your-self.
    Attached Files Attached Files

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

    Re: Button Required to Send Updated/Historical Data to Appropriate Sheets in Workbook

    Wow, that is genius! Thank you! The unmerged cells on the Entry Page there are no problem at all, that's great! I did come across a few issues, which I've listed as follows:

    1. When I edit the information for an existing item number and click on Submit, the entry page data gets cleared like it should, and it also gets entered in the Current List in numerical order. However, at the Historical List sheet, another copy of the previous record has been added there instead of a copy of the new one. I don't know whether this is a good idea or not, but would it be better to have code set up to have the data from the Date Entry page added to the Historical List first, and the Current List be set up to show only the most current information from there?

    2. Submitting an entirely new item number with it's information gets added wonderfully to the Current List, but not to the Historical List. Maybe what I mentioned in #1 would help with this too?

    3. Upon entering a current item number I get the prompt, but when I click No, all the data is cleared and I am unable to view the information for the item. I think that it would be better to have the message box appear upon attempting to change the information for a current item - so, for example, when I enter the number of an item in the Current List and hit Enter, it's information would be displayed, and the message box would only prompt the user if they tried changing the information belonging to that item.

    4. At the moment, if I'm editing the information of a current item number and realize for example that I've been trying to edit the wrong item - and try to change the number to a different/new item I get the prompt and say Yes, only to have the item number revert back to the previous one. Perhaps changing when the message box appears as I suggested in #3 might help with this as well?

    Other than those few 'bugs', it looks like it's going to be great! If there's anything I wasn't clear enough on, please let me know. And a million thanks for all you've done already!

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

    Re: Button Required to Send Updated/Historical Data to Appropriate Sheets in Workbook

    This is so close - is there anyone who can help me with the final tweaking of this code? Thank you so much..

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

    Re: Button Required to Send Updated/Historical Data to Appropriate Sheets in Workbook

    Anybody have any suggestions here? I urgently need this to work.

    Thanks for any input, and a Merry Christmas to all!

+ 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