Solved: Drop Down List

[chem101]

Good Morning Everyone,

I've set up a drop down window that shows a list of 4 items that the user can select from. The list is setup through Data Validation. I want to limit the total number of selections a user can make from this list to 4. I'm not concerned with the combinations selected. I just want to limit the number of items they select to four items. The code I have for this list is:

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 3, 7, 12, 16, 17, 20
If Target.Offset(0, 2).Value = "" Then
lRow = Target.Row
Else
lRow = Cells(Rows.Count, lCol + 2).End(xlUp).Row + 1
End If
Cells(lRow, lCol + 2).Value = Target.Value
End Select

End If
I appreciate your help.

Thank you.

[Bob Phillips]

How does this differ from http://www.vbaexpress.com/forum/showthread.php?t=35368

[chem101]

Given the correspondence we had previously, I wasn't sure that you understood what I wanted to do. Also, I had difficulty with the code you had sent to me.

[Bob Phillips]

So talk about that, it is not good to just ignore it.

[chem101]

Thank you, I appreciate your patience. As we discussed earlier I have a drop down list setup through DV. The user can select one of the four items from the list and the code that's in place displays the users selection in a cell 2 columns to the right. Currently the user is able to make as many selections as they wish 10, 100, or 1000. The code adds the user's selections to the list 2 columns to the right and one row down. I also have a reset button below the DV list to clear the cell contents. What I would like to do if possible is to limit the users selection from the DV list to a total of four items. The code I have in place is as follows:


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 3, 7, 12, 16, 17, 20
If Target.Offset(0, 2).Value = "" Then
lRow = Target.Row
Else
lRow = Cells(Rows.Count, lCol + 2).End(xlUp).Row + 1
End If
Cells(lRow, lCol + 2).Value = Target.Value
End Select

End If

I also have drop down lists through DV in columns 3, 7, 12, 16, 17, and 20. I would like to limit these selections as well but I thought I would just try to finish column 3 (total of 4 selections). If I can provide any additional information please let me know.

Thank you again for your assistance.

[Bob Phillips]

Option Explicit

Private prevVal As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C:C" '<<<< change to suit
Dim cellName As String
Dim cnt As Long

On Error GoTo ws_exit

Application.EnableEvents = False

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

cellName = "_cnt_" & Target.Address(False, False)
cnt = GetValue(cellName)
If cnt < 4 Then

prevVal = Target.Value
cnt = cnt + 1
ThisWorkbook.Names.Add Name:=cellName, RefersTo:=cnt
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

Private Function GetValue(ByVal cnt As String)
On Error Resume Next
GetValue = Application.Evaluate(ThisWorkbook.Names(cnt).RefersTo)
End Function

[chem101]

Thank you for all of your time and effort but I'm going to use Conditional formatting with a msgbox to accomplish this task.

Thank you again!