Validation morethan once in same Column

[Kumar]

I Have a data which Contains 3 columns, Ist Column Contains Date, 2nd Column
Contains Employee Names and IIIrd Column Contains Time... The IInd Column
which Contains Employee Names is a Validation List .... Now i want one more
validation in the IInd Column, where by the Employee should not be repeated
morethan 3 times.. can i apply morethan one validation in IInd column.. any
one please help me...

 

[Gary''s Student]

Hi Kumar:

Say that column C is the column for entering names and the list is stored
elsewhere:

James Smith
John Johnson
Robert Williams
Michael Jones
William Brown
David Davis
Richard Miller
Charles Wilson
Joseph Moore
Thomas Taylor
Christopher Anderson
Daniel Thomas
Paul Jackson
Mark White
Donald Harris
George Martin
Kenneth Thompson


We want an additional rule that says that a name, say James Smith, can be
entered at most three times in the column. Enter this small event macro in
the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set c = Range("C:C")
If Intersect(t, c) Is Nothing Then Exit Sub
n = Application.WorksheetFunction.CountIf(c, t)
If n > 3 Then
Application.EnableEvents = False
t.ClearContents
t.Select
MsgBox ("You have already used this name three time" & Chr(10) & "pick
again")
Application.EnableEvents = True
End If
End Sub

This will prevent a name being selected four times.


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Kumar]

Thank you Gary.. It Worked... but can't we use this without any VBA....It
Helped me a Lot...

 

[Gary''s Student]

This is a good example of allocating functions. We use Data Validation to
implement the pull-down and an Event Macro to do the count check.