PDA

View Full Version : Solved: Drop Down List



chem101
12-28-2010, 08:03 AM
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
12-28-2010, 08:17 AM
How does this differ from http://www.vbaexpress.com/forum/showthread.php?t=35368

chem101
12-28-2010, 08:49 AM
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
12-28-2010, 10:13 AM
So talk about that, it is not good to just ignore it.

chem101
12-28-2010, 12:12 PM
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
12-28-2010, 02:03 PM
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
12-29-2010, 01:04 PM
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!