• Excel User Form & Option Button Group

    Author
    Topic
    #468102

    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

    Viewing 11 reply threads
    Author
    Replies
    • #1217844

      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.

    • #1217892

      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

    • #1217940

      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.

    • #1217943

      What happens to the data after the order is posted.
      This is really a task better done by a database than a spreadsheet.

    • #1217948

      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.

    • #1217959

      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.

    • #1217969

      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.

      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

    • #1217972

      OK.
      Thanks for Info.
      Let me have a look and mull it over
      Then hopefully can give you a few ideas.

    • #1218020

      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

      The advantage being that NO Macros are necessary

    • #1218086

      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

    • #1218097

      See attached Picture

    • #1218180

      This is great info and works perfectly.

      Again, thanks so much for your assistance!!
      C

    Viewing 11 reply threads
    Reply To: Excel User Form & Option Button Group

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: