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