Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    list which can be added to (excel 2000)

    I am wondering if anyone know if its possible to make a list box or combo box which hold a list of things but also if that thing is not in the list the user can simply type it in instead of editing the list attached to the list or combo box?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: list which can be added to (excel 2000)

    Do you just want the user to be able to enter a value that is not in the list? That can be accomplished with Data | Validation, type List, by not displaying an error message. You can also use the combo box from the Control Toolbox and set its MatchRequired property to False (this is the default).
    Or do you want the value entered by the user to be added to the list? That would be much more complicated.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list which can be added to (excel 2000)

    HI Hans,
    I have tried the combo box and list box approachesbut not an idea how to change thouse properties. I right clicked and looked at the properties but found none that you mentioned. but unfortunetly I wanted to know if its possible to add items to the list. If a user clicks a drop down menu and looks for the list item and sees its not there they can type there list item in and for the future they will now have it in there list.

    I was thinking of adding a extra spreadsheet which would hold lists. each list in a column, named so a button could reference them.
    the buttons would be on the data entering sheet. everytime a user would like to fill in a blank cell. they click the corresponding button to that cell which would post that list for that button in column J. From this list the user would click the item they want and click the buttom agtain which would enter the choice and hide the list just brought up.
    This might be more work. I am working on something around these lines. Do you think its too much, is there a simplar approach like using hide-show columns?

    thanks

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

    Re: list which can be added to (excel 2000)

    You can define dynamic ranges and use those as Input range for the combo boxes (you used combo boxes from the Forms toolbar, not from the Control Toolbox mentioned in my reply).
    The user won't be able to type a non-existing value in the combo box, but if a new item is added below the current input range on the Lists sheet, it will automatically be displayed in the combo box.
    See attached version.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list which can be added to (excel 2000)

    OK, So to add the combo box I simple goto VEIW>Toolbars>Contol Tool Box?

    then select the combo box and place it where needed.

    When I right click on the box the properties you mentioned are not there.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: list which can be added to (excel 2000)

    My latest reply was based on the workbook you attached, with combo boxes from the Forms toolbar, not from the Control Toolbox.

    If you create a combo box from the Control Toolbar, you must first make sure that Design Mode is on (it's the first button in the Control Toolbox must be "down"). Then right-click the combo box, and select Properties from the popup menu.

  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

    Re: list which can be added to (excel 2000)

    Does this example help?
    The item is added in the next cell and the combox listfillrange is updated when the combobox loses the focus.

    Steve

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list which can be added to (excel 2000)

    Thanks guys. Steve Im not 100% sure how your example works.
    Arer combo boxs able to add items if they are not in the specified list to their list?
    Is there any know web sites that talk about the combo boxes properties and what they do?
    Thanks again

  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

    Re: list which can be added to (excel 2000)

    The list is in cells A1:A4 (ListFIllRange)

    If you select an item with the combobox it will add it to cell C1 (LinkedCell). This is the "typical behaviour" that is easy to emulate in various ways.

    If you enter an item not in the combobox listfillrange, when the combobox loses the focus, (eg by selecting something else) the code will see it is not in the list and inform you that it is not in the list and ask if you want to add it. If no, the item still remains in C1 but is not added to the list.

    If, you select "Yes" to add it to the list, the item will be added down 1 row from your current list and expand the listfillrange to include the new item.

    Typically I just use the HELP. RIght -click on the object and select "properties". select the property in the window and press <F1>. It gives the syntax and an explanation and if you press "example" it displays some example code. But perhaps others will post some websites they have found helpful.

    Steve

Posting Permissions

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