by Sisyphus » Sat Apr 16, 2016 3:46 am
I can tell you how it might be done, but I can't provide the code for you because I am just passing through today. You are right in presuming that VBA will be needed.
Create an event procedure for the drop down's change event. If your drop down is created by validation you can use the cell's change event. If it is an ActiveX control you can use the ListIndex property instead of the Value property. In the following I presume that you have a validation drop down.
In your event procedure create the following variables.
Static Repeats(4) as Boolean. ' this is zero-based for your 5 choices
Static means that the variable will retain its value between calls. If you need to shut down Excel during your exercise the values written to Repeats() here must be written to the workbook instead.
- Code: Select all
Dim Dd() as String
Dim f as Integer
Dd = Split("A,B,C,D,E" ,",") ' replace A to D with your 5 choices. They may not contain commas
For f = UBound(Dd) To 0 Step -1
If StrCompare(Target.Value, Dd(n), vbTextCompare) = 0 Then
Repeats(f) = Repeats(f) + 1
Exit For
End If
Next f
If f < 0 Then
MsgBox "No match found" ' there is a difference between Dd and drop down
Else
If Repeats(f) > 15 Then
' you can change the text to your liking
MsgBox "No room." & Chr(10) & _
"Make another choice."
Else
MsgBox "You are counted in."
End If
End If
Last edited by
pecoflyer on Sat Apr 16, 2016 5:34 am, edited 1 time in total.
Reason: Added code tags
Have a great day!
Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)