Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jun 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    inserting combo box data

    Making a simple to-do LIST with 25 categories. Is there a way to display the entire list without scrolling to select and enter the category name? I created the combo box, linked the categories--they all show up. Now how do I actually ENTER the category on the line with the specific task by clicking on the category from the combo box? Or is there another easier way. I am trying to avoid scrolling when selecting the category. Thanks for your help--I have a couple of other ideas for this sheet.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Welcome to the Lounge.

    Just a suggestion. Skip the Combo Box and Instead Use Auto-complete.
    If possible make each of the 25 items unique, by example:
    Original List
    Machine Repair
    Machine Replace
    Machine Down Time
    Machine Run Time
    Revised List
    Repair Machine
    Replace Machine
    Down Time – Machine
    Run Time – Machine
    Then put the list above the input lines leaving no empty rows between the list and the first input cell
    Now by typing the first letter or first letters Auto-Fill will find the right Category and enter it in the current cell.
    HINT: If there has to be blank rows between the Categories and the Input put a Period in the blank cells
    Also you may want to use View and Freeze the list of Categories at the top of the Worksheet.
    As long as the Category Names are unique this will allow fast and accurate input.
    Hope this helps.

  3. #3
    New Lounger
    Join Date
    Jun 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I'll try that. But if I list the 25 category names in a column above and freeze so that I see them all the time (who knows, maybe I can memorize them), I'm limiting the amount of screen size I have left below. I'm guessing they don't have to be in the same column? Maybe spread accross the top into other cols?

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Your Idea is very good but needs some refinement.

    Since auto-complete needs all of the informaiton to be in one column you may try this:

    At top put in one column all 25 Categoires. Next hide as many rows as you like (say 15) Now the top of the screen has 10 rows. Use the other columns and copy the hidded Category items so they are visible to you.

    Hidding the rows should not effect Excel's auto-completion and you save top side space.

    Of course, you can hide more than 15 rows if you like.

    hope this helps.

    Tom D

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What kind of combobox did you choose? If you are using the simple one from Data-validation it seems to be hard-coded to 8 displayed.

    If you use an Active-X, you can adjust the number of rows to display in the properties. There are some examples with code at http://www.contextures.com/xlDataVal10.html
    Steve

  6. #6
    New Lounger
    Join Date
    Jun 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    We're heading in the right direction. I used the active x combo box and it displays all categories--but I don't know how to insert the category I select on the line (any line) in the category column. Is there some formula I need to place in the col cells?

    Hiding the categories is a good idea--remembering them is hard, although I can keep a list on my pc. But...what about using one worksheet as a "data entry" sheet and having the tasks with categories automatically posted to another worksheet so that I can see the entire sheet, do sorts based on priority, or categories or date if I want to have a list of the most recent tasks entered. On the data entry sheet it would be nice to somehow have the current date (and only the date task created) automatically entered whenever a task item is entered in the task cell.

    So, on the data entry sheet I would enter, in this order: category, priority, date, task in their respective columns and then each task would be posted to the next worksheet where I can do my sorts. (Hmmm, maybe have a worksheet for each category and simply have task posted there automatically--and those worksheets would only need Priority, Date, and Task since we already know the category.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Look at the link to contextures web site that I posted....

    Steve

  8. #8
    New Lounger
    Join Date
    Jun 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, didd. Thanks duthiet--I finally got what you mean. Steve I never did vba before. Your link...does it mean I would have a formula in every cell in the category col to accept what is in the combo box? I like my idea of posting the tasks to another worksheet--one into which I can import lists of tasks I already have. (but that might not work since i would still have to add a category for each task. Seems like excel should make it easier to make a simple task list. Why doesn't excel simply allow more items to be seen in data validation?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The macro does not require formulas into cell. the macro puts the values into the cells from the combobox. You really don't have to do the VBA, it has already been written on that page with instructions on how to use and an example file for download.

    It is a matter of choice:
    1) Live with scrolling in the datavalidation for anything over 8 items
    2) Add the list of 25 items in the column (outside the datatable, but connected so autocomplete of the list works
    3) Do some kind of programming to use a more versatile combobox.

    Item1 is the simplest, item2 seems clunky and any gaps within the column can defeat the autocomplete. Item3 is more complex and also has the disadvantage of eliminating UNDO from the worksheet.

    Steve

Tags for this Thread

Posting Permissions

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