Re: Duplicate filed Warning

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 03/11/04 

Date: Thu, 11 Mar 2004 16:58:41 +0100

Hi Indy
this can be done using an event procedure.
Assumptions:
- you have a list of valid names somethere on your ***
- in column A you have used 'Data - Validation' to create a drop down
list (see http://www.contextures.com/xlDataVal01.html for how to do
this)
- You only want to have each name only onced in column A.

Put the following code in your work*** module (see
http://www.cpearson.com/excel/events.htm for more infos about event
procedures)

Private Sub Worksheet_Change(ByVal Target As Range)
    Set Target = Application.Intersect(Target, Range("A:A"))
    If Target Is Nothing Then Exit Sub
    On Error GoTo ErrorHandler
    Application.EnableEvents = False
    Dim AWF As WorksheetFunction
    Set AWF = Application.WorksheetFunction

    If AWF.CountIf(Columns(Target.Column), Target) > 1 Then
       MsgBox "Value is already inserted in column A" & _
              Chr(13) & "Please choose a different one"
       Application.Undo
       Target.ClearContents
    End If

ErrorHandler:
    Application.EnableEvents = True
End Sub 

--
Regards
Frank Kabel
Frankfurt, Germany
Indy wrote:
> I make a schedule for instructors using excell.   WHat happens is,  i
> assign a Block of instruction to an instructor.  Is there a way that
> i can  make the form look for muliple names on the same day.      The
> True objective is to have a Drop down list for each block,  One a
> name has been entered on that day of instruction,  i would like it to
> disappear so that i can't use it again on that block.
>
> Is any of this possible.     The least i would like to accomplish is
> to have an error message popuo saying that a name already exists for
> that day.
>
> Thank you,
>
> Indy