Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    You make it look so easy! This is awesome!
    Thanks Hans!
    Lana

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='LJM' post='791591' date='31-Aug-2009 17:03']You make it look so easy! This is awesome!
    Thanks Hans!
    Lana[/quote]

    Further to Hans' approach, the attached workbook achieved your original intent.
    Attached Files Attached Files
    Regards
    Don

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts
    This one is awesome as well! I'll try this one out too!
    Thanks so much!!
    Lana

Posting Permissions

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