Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Apr 2010
    Location
    Virginia, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [attachment=88497:Excel Order Form Screenshot.doc]

    I'm creating an order form with a couple dozen products - and each product can be produced in 4 different colors. Each product in each color has a different product code. (And assume each user will only want to order products in one color)

    I'd like to have a User Form in excel where the user will be able to select which color they would like to order from a group of 4 option buttons. When a button is selected, and the user clicks "OK" I'd then like the products codes associated with that color to populate on the order from. (The form visible to the user and a full list of all [hidden] product codes could be on the same tab of the workbook)

    Is this a logical setup, or is there a more simple way to do it? If this is the best approach, any assistance on the code (or reference to another existing post) for the option buttons would greatly appreciated!! A simple screenshot is attached in a word document.

    Thanks in advance,
    C
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'd suggest to do the selecting of the product on the same form as where the color needs to be selected, or to do the entire selecting on the worksheet.

    You could simply use data validation to create dropdowns in two adjacent cells.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Apr 2010
    Location
    Virginia, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's actually how I have the order form set up now, but unfortunately selecting the correct form numbers is not as simple as selecting a "color" and we need a better way to set up the form. I only chose to call them colors so the message board discussion would be simpler Either way though, the information in the order form is pretty basic, it's just a matter of presenting it to the user in a way that isn't overwhelming for them.

    Is there any way to have these option buttons work?

    Thanks again!
    C

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Why not include a real-life example? You can still change the product names and such so we don't know what products this is about.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    What happens to the data after the order is posted.
    This is really a task better done by a database than a spreadsheet.
    Andrew

  6. #6
    New Lounger
    Join Date
    Apr 2010
    Location
    Virginia, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Pieterse - this is a real life example and this is how the form will be set up. Whether the person is ordering apples or oranges or colored objects is irreverent. I am just trying to understand how to populate the form numbers based on which "color" the user wants to order.

    AKW - the order form is processed by someone after it is filled out, and the information is used to manually place orders and populate other databases. Those processes are firmly fixed, so adjusting this form is really the only improvement I'm able to make.

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I think what Pieterse meant was.
    Can you post a spreadheet example showing where the Order data would go,
    and at what point the Form would be triggered to select colour.

    For example does entering the QTY make the coloured form appear.
    Is there only 1 colour per Paper
    Is the code to go into the Form# box
    Are there only 10 Paper Options
    Does it have to be done with Option Buttons or could you use a Drop Down for the colours
    What is meant to happen when you click on OK button
    Are these the actual codes or is this a Mock up set of codes?

    I can see the idea of what you are wanting to do,
    but the actual mechanics of entry for the end user determines the method of processing.

    For example if they are ordering 5 Items, do you expect the Colour Option Form to be run once for each,
    or would it be better to have items all processed on one form.

    Sorry that seems like an interrogation, but just trying to get the full picture to come up with a suitable solution.
    Andrew

  8. #8
    New Lounger
    Join Date
    Apr 2010
    Location
    Virginia, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh I don't mind the questions at all - I appreciate the help!

    So - I have attached the form that I am trying to bold out. There eventually will be around 150 different papers (products), each with 4 different possible form numbers. I'm simultaneously working to pull the paper names and form numbers from other reports to build this order form. [I really only care about the form numbers, where the user only cares about the name of the paper and not the form numbers.]

    The user will only order one "color" paper per order, so I initially thought the User Form would be triggered as soon as the order tab is clicked on or the workbook was opened, but it would make sense to have it trigger when a QTY is entered too. Because the user will only want one color per order, it would be fine for the user to pick the color on the user form one time.

    The reason I am considering option buttons is because having the user select a form number for each paper they want to order is confusing for them, especially when they want all 150 papers in the same color. That means that on the current form with the drop down selections beside each paper name (drop down lists the 4 form numbers for the 4 different color options) the user has to correctly select the form number from each of the 150 drop down lists. Because the form numbers don't make sense to the user, I have to manually check each from number before I place the orders. My logic for trying to set up the option buttons is that if the user can select the color paper they want first thing / right off the bat, then there is less confusion on their part and more accurate orders that come across my desk. Unfortunately, circumstances prevent me from having separate order forms for each color; therefore, I need one order form for every user.

    I am new to this type of form creation, but what I gather form other posts on this site and other sites is that I need to input code for each of the option buttons (or the group of buttons rather) and the OK button. Please excuse my terminology if it's incorrect, but this is what I am thinking might work... I just don't know how yet

    If Red is selected, input data from F16 through F25 into C4 through C13
    If Blue is selected, input data from G16 through G25 into C4 through C13
    If White is selected, input data from H16 through H25 into C4 through C13
    If Black is selected, input data from I16 through I25 into C4 through C13

    When the OK button is clicked, cells C4 through C13 are populated based on the color selected.

    This way the user doesn't have to bother with the form numbers, because they are already there after the color is selected from the user form. When I receive the file from the user, I can essentially copy/paste the rows with the paper name, form number, and quantity to the other databases, etc.

    Thanks!
    C[attachment=88510:Copy of Form update_4.11.10.xls]
    Attached Files Attached Files

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    OK.
    Thanks for Info.
    Let me have a look and mull it over
    Then hopefully can give you a few ideas.
    Andrew

  10. #10
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Not sure if this is what you want, but have a look at the attached.
    It has an Option Group Control on the form to select the colours
    This is linked to a cell on the codes sheet.
    The Actual Codes are looked up with a VLOOKUP
    using the Selected Option Button to give the lookup columns

    [attachment=88513:OFImage.jpg]

    The advantage being that NO Macros are necessary

    [attachment=88514:Order Form update_4.12.10 (No Form).xls]
    Attached Images Attached Images
    Attached Files Attached Files
    Andrew

  11. #11
    New Lounger
    Join Date
    Apr 2010
    Location
    Virginia, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Actually, this is Perfect and will probably work much better than my original idea!! Thank you for taking the time to put this together!

    I wasn't aware VLOOKUP could be used in this way - how would I change the range specifications (to add additional form numbers to the list?)

    Thanks again,
    C

  12. #12
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    See attached Picture

    [attachment=88517:SettingRanges.jpg]
    Attached Images Attached Images
    Andrew

  13. #13
    New Lounger
    Join Date
    Apr 2010
    Location
    Virginia, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is great info and works perfectly.

    Again, thanks so much for your assistance!!
    C

Posting Permissions

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