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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.