Provide Decreasing Data Validation Lists

Wouldn't it be useful if you could give users a list of options to select that decreases as options are used? You can create this nifty feature with the use of Data Validation and some Excel VBA code.

Say you have one spreadsheet that many users input into. Each of these users needs to select an item from a validation list on the spreadsheet. After each user has selected their items, wouldn't it be great if the validation list only showed what was left (the items that had not yet been selected). This would make data entry easier and more efficient for all, and prevent duplicates of data.

On any worksheet, add your list-say, in A1:A10. Now, select this range and click in the Name box (left of the Formula bar), type the name MyList, and press Enter. This will give you a Named Range called MyList.

Now, select the range on another worksheet where you would like the validation list to go. Right-click this sheet name tab, select ViewCode, and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)Dim strVal As StringDim strEntry As StringOn Error Resume NextstrVal = Target.Validation.Formula1If Not strVal = vbNullString ThenstrEntry = TargetApplication.EnableEvents = FalseWith Sheet1.Range("MyList").Replace What:=strEntry, _Replacement:="", LookAt:=xlWhole, _SearchOrder:=xlByRows, MatchCase:=False.Sort Key1:=.Range("A1"),Order1:=xlAscending, _Header:=xlNo, OrderCustom:=1,MatchCase:=False, _Orientation:=xlTopToBottom.Range("A1", .Range("A65536").End(xlUp)).Name = "MyList"End WithEnd IfApplication.EnableEvents = TrueOn Error GoTo 0End Sub

Close the window to get back to Excel. Now, select Data → Data Tools → Data Validation (pre-2007, Data → Validation), and choose Allow: → List. Then, under Source:, enter =MyList and click OK.

Now, when you select a name from the list, this name no longer appears on the drop-down.

Notice that we have referred to the named range MyList as Sheet1. Range("MyList"), preceding the named range with its sheet codename. We have done so because the reference to the named range (MyList) is in the private module of another Worksheet. Without it, Excel would assume MyList is on the same worksheet as one where the Worksheet_Change code resides.