Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, I am trying to create an order form in Excel showing values like the following:

    WCT-TK1 Nicola Tank Navy Small $30.00

    I used to program in Access and this would all be very easy.

    What I am trying to achieve is to have the user click a drop down list like in Access and the user would see all the inventory, but if he/she wanted to just see a certain product, for example, the Nicola Tank as above, they could simply type a "N" and the N products would show up, then they could select the Nicola Tank in Navy or Black or whatever in a Small, Large or whatever size. I want the corresponding cells to populate with the above information. Easy in Access of course. Right now we have a huge form that shows all of the inventory and the user just ticks of products that they want and it all gets added up like a regular order form would. I still want it all to get added up, but I only want the order form to show what they want, not every single product available. If I am confusing, it looks like a grid with all the products and sizes and the customer would put X's in selecting the sizes for the products they want and would type in the number of how many of that particular product they want and it gets all added up. But, because we have so much inventory, I want to make it easy for the customer and have a drop down list of all the inventory (I would create on a different spreadsheet) and the customer could select what they want. I do know some VBA for Excel, but have not used it in a while so need a little refreshing. Please help, I would greatly appreciate it ever so much.

    Cheers!

    Tina

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Excel provides three types of dropdown lists that you can use on worksheets:
    1. You can set Validation (from the Data menu) to allow entries from a list. Unfortunately, this doesn't provide for AutoComplete, i.e. typing the first characters of an entry won't work. The dropdown list displays only one column.
    2. A combo box from the Forms toolbar. The dropdown list can display multiple columns. AutoComplete is not supported.
    3. A combo box from the Control Toolbox toolbar. The dropdown list can display multiple columns, and AutoComplete is supported.

    Having a combo box from the Forms toolbar or Control Toolbox in each cell needing a dropdown would be rather tedious. There are workarounds, but they require advanced VBA programming.

    Another option is to use John Walkenbach's free Enhanced Data Form. As explained in Data Form Customizing, you can set it up so that users can select items for a field from a dropdown list (with AutoComplete functionality).

    Finally, you could create your own userform in the Visual Basic Editor. But to "bind" this to data in the worksheet in a flexible way requires a considerable amount of programming.

  3. #3
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks so much. I have used Combo boxes from the control toolbar. This is what I need. I guess I just need to refresh my memory and making the following work:

    Being able to copy the Combo box down to another cell, only if needed;
    If the user types an "A" for example then all of the "A" products would show;
    Have the Combo box linked to the cell;
    Have the Combo box be "filled" with the data (can it be stored on another worksheet);
    When the Excel sheet is printed out, will it show the "Combo box" outline?

    I would love if you could provide assistance - I have done this before, but have not done it in a couple of years so I just need refreshing.

    Kind Regards,
    Tina

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the attached workbook.

    Column A uses a single combo box from the Control Toolbox. There is code in the worksheet module that displays the combo box over the active cell and that makes the combo box display the value of the active cell and vice versa. The combo box will not be printed.

    Column C uses Data | Validation to display a dropdown list in the active cell. No code is needed.
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks so much, that is awesome! I hope I am not driving you nuts, but what if the user selects a value and then change their mind, how can the value be deleted? I tried to, but it will not let me. I know I could add a blank cell to the drop down I suppose.

    One more thing - I would love the drop down to show multiple values and then once the user selects the values he wants, the other cells to be populated with those values:

    E.g. Pullover sweater Blue Size Small $30.00

    Is this too much?

    Thanks so much for your help.

    Tina

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It can be done, but it's complicated and makes your workbook harder to use. Perhaps a userform would be better. Or use Access!

  7. #7
    Lounger
    Join Date
    Apr 2009
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your input again. Yes, perhaps a user form would be better because I do know how to connect a user form to Excel.

    Thanks,

    Tina

Posting Permissions

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