Results 1 to 10 of 10

Thread: List Box (2000)

  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Box (2000)

    Is there a way to limit a list box to cells that have not already been selected. Ex:

    list box in A:A is linked to D14 (contents a,b,c,d). If someone selects "a" in the list box, how can we prevent them from selecting it a second time?

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

    Re: List Box (2000)

    I don't understand. Do you really mean a list box? Nothing really changes if the user selects the same value again, does it?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box (2000)

    Basically something along the lines of what's in the attachment. Where the choices in the validation list box in column A are limited to only the values in column C which do not contain blank text.

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

    Re: List Box (2000)

    What you refer to is not called a list box, but a dropdown list, or perhaps a combo box. Henc my confusion.

    But I am sorry, I don't know how to do what you want.

  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

    Re: List Box (2000)

    The only way I can imagine doing it is to create the comboboxes all individually. Each one when selected would check the master list and only add the items "available" for selecting to the list (via code)

    The problem is that once all the items have been selected, none of them could be changed since there would be nothing new to select, though you could add a button to "reset" them all.

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: List Box (2000)

    You could delete the cell containing the value selected so that next time it will be missing from the validation list. The Worksheet Change event can be employed here. I think using named ranges might be best and in the following example I have named Range A2:A13 as Inputs and C2:C13 as SelectList. This range would just hold the values (no need for countif formulas). The following code (no error checking included) should then take care of the validation list :<pre> Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [Inputs]) Is Nothing Then
    [SelectList].Find(Target.Text, LookIn:=xlValues).Delete
    End If
    End Sub</pre>

    I attach an amended version of your workbook with this implemented.

    You will of course need to re-engineer the original list should you need to reset.

    Andrew C

  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 Box (2000)

    If you type in the item the it works fine. But if you use the pick list to select (as many people might) the data validation does not trigger a change event and it does not work.

    I think you could do an approach like this, but use the comboboxes and not validation. I do not think there is any real good way to "capture the validation event".

    Steve

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: List Box (2000)

    Steve,

    It works fine for me with both XL 2000 and XL 2002.

    What version of XL did you try it on ?

    I would agree however that the use ComboBox(es) would be a better choice.

    Andrew C

  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 Box (2000)

    I only use XL97.

    In XL97 (presumably this was changed in later versions) when you select from a datavalidation list, the change event does not get triggered.

    Steve

  10. #10
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box (2000)

    What am I missing? Your example is working fine the way it is. Are you trying to do this using VBA?


    yoyoPHIL

Posting Permissions

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