Stop Duplicate Entries.

[elnunezgib]

Hi All, looking for some advice or assistance.
I am creating a desk booking spreadsheet.
On this workbook, I have the list of names on Sheet 1. On Sheet 2, column A will be populate will be desk (e.g. Desk 1 in Cell A1, Desk 2 in Cell A2, etc, etc.) and column B will have the data validation list from sheet 1 (A1:A50)

How can I "program" excel so that when data is selected on column B (List From Data Validation), I would like to stop duplication to occur on that same column further down the rows, i.e. stopping same person booking two desks

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for Sheet2 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column B.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    If WorksheetFunction.CountIf(Range("B:B"), Target.Value) > 1 Then
        MsgBox ("You have already booked a desk.")
        Target.ClearContents
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub