PDA

View Full Version : Solved: Drop Down List



chem101
12-13-2010, 10:09 AM
Hello Everyone,

I would like to limit the number of selections a user may select from a drop down window list. If there are five items in the drop down list to choose from I want to limit the user to only five selections. I don't want to limit their selections, meaning they can select one item more than one time, I just don't want them to be able to choose more items than is on the list. Here's the code I have so far:

Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Select Case Target.Column
Case 13, 16, 17, 20
If Target.Offset(0, 1).Value = "" Then
lRow = Target.Row
Else
lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1
End If
Cells(lRow, lCol + 1).Value = Target.Value
End Select

End If

Thank you for any help you can provide!

Bob Phillips
12-13-2010, 10:19 AM
Surely, a DV automatically limits them to however many choices are in the list, so I don't get what you are asking.

chem101
12-13-2010, 10:36 AM
I want to prevent the user from an option 100 or 1000 times.

Bob Phillips
12-13-2010, 10:59 AM
That didn't really clarify anything for me.

chem101
12-13-2010, 11:07 AM
ok, lets say we have a drop down list containing items A, B, C, and D. I want to limit the user to a total of four selections. They can select any of the four items they want. They can even select the same item more than once, even four times. What I want to limit them from doing is selecting item A five times then item B eight times then item C 50 times and so on. I want to limit the maximum number of selections for this particular drop down list to only four selections.

Thank you for you assistance.

Bob Phillips
12-13-2010, 11:14 AM
So, can they select A 4 times AND B say 3 times, or just 4 times between them?

chem101
12-13-2010, 11:19 AM
Currently, they can make as many selections as they want. I want to limit their selections to only four.

Bob Phillips
12-13-2010, 11:29 AM
Option Explicit

Private prevVal As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D1" '<<<< change to suit
Static cnt As Long

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

If cnt < 4 Then

prevVal = Target.Value
cnt = cnt + 1
Else

Target.Value = prevVal
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
prevVal = Target.Value
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.