List Box (2000)

Topic

New Reply

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 D1:D4 (contents a,b,c,d). If someone selects “a” in the list box, how can we prevent them from selecting it a second time?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 :

    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

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

yoyoPHIL

Reply | Quote