Excel 2007 – Drop down list

Topic

New Reply

Hi,
I’m using a data validation list in order to have a list of names for a user to select, however there is a twist. I’d like for each name to be able to only be selected ONCE. So if “Kelly” is chosen in cell B1, then it can NOT be selected for cells B2-B7. Ideally, I wouldn’t want it to even show up as an option in the drop down box. I’m guessing the use of data validation is not the route to go??? Any ideas is much appreciated! I’ve attached a worksheet with an example.
Thanks!
Lana

Reply | Quote

Replies

Restricting the lists to items not yet selected would probably take a lot of VBA code (that kind of thing is slightly easier in Microsoft Access).
You could use the Worksheet_Change event in the worksheet module to prevent duplicate entries:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim oCell As Range
  If Not Intersect(Range("B1:B7"), Target) Is Nothing Then
	For Each oCell In Intersect(Range("B1:B7"), Target).Cells
	  If Application.WorksheetFunction.CountIf(Range("B1:B7"), oCell.Value) > 1 Then
		oCell.ClearContents
		MsgBox "Please select a unique item!", vbCritical
		Exit Sub
	  End If
	Next oCell
  End If
End Sub

Reply | Quote

You make it look so easy! This is awesome!
Thanks Hans!
Lana

Reply | Quote

You make it look so easy! This is awesome!
Thanks Hans!
Lana

Further to Hans’ approach, the attached workbook achieved your original intent.

Reply | Quote

This one is awesome as well! I’ll try this one out too!
Thanks so much!!
Lana

Reply | Quote