New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

limit the # of times an option from a drop down list is selected....

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

limit the # of times an option from a drop down list is selected....

Postby mfalvarez » Thu Apr 14, 2016 11:19 am

Hello,

Can someone please explain to me how to limit the number of times an option from a drop down list can be selected?
I am creating a very basic table for a potluck lunch, and I have created a sign-up sheet in which the guests will select what they will share; it is a drop-down list with 5 selections. I would like to limit each selection to 15, I mean only 15 people will be able to select each one of the options in the drop down list.

Is it possible to do that in excel?, (I am sure it is, but I have no clue how... I have used conditional formatting but it doesn't seem to work).

Your help will be very appreciated!
MFA
  • 0

mfalvarez
Rookie
 
Posts: 1
Joined: Apr 14, 2016
Reputation: 0
Excel Version: 2010

Re: limit the # of times an option from a drop down list is selected....

Postby 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
  • 0

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! :D

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)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 86 guests