I use the following code, written by Philip Treacy, and it works very well. My question is: now that my DropDown list has expanded, I would like to see more than the 8 rows that a DropDown list is limited to. For this I need to use a ComboBox but I cannot make the ComboBox work with Philip's code. Is there any way that this ban be achieved?
Mel
Philip's code is:
Sub DVChange(ByVal Target As Range, ByVal SheetDV As Range)
' Written by Philip Treacy
Dim OldVal As String
Dim NewVal As String
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, SheetDV) Is Nothing Then
Application.EnableEvents = False
NewVal = Target.Value
On Error Resume Next
Application.Undo
On Error GoTo 0
OldVal = Target.Value
If OldVal = "" Then
Target.Value = NewVal
Else
If NewVal = "" Then
Target.Value = ""
Else
If InStr(Target.Value, NewVal) = 0 Then
Target.Value = OldVal & ", " & NewVal
End If
End If
End If
Application.EnableEvents = True
Else
Exit Sub
End If
End Sub
Hi Mel,
Why are you saying that a dropdown is limited to 8 rows?
Please prepare a sample file that works with the code, we cannot recreate and test what you are saying.
What I meant by 8 rows is that only 8 rows of the DropDown list is visible whereas in a ComboBox one can set this to, say, 24 etc.
Can you provide the sample file?
As you can see from my first post, all I want to do is use a ComboBox instead of the DropDown because the list within the DropDown has grown and I would like to be able to see more than the 8 lines that is the D/D limit
Hi Mel,
I understand what you need, but you also have to see things from our point of view.
You are saying that:
"For this I need to use a ComboBox but I cannot make the ComboBox work with Philip's code. "
I need to understand why you're saying that you cannot make it work: what error do you receive? In what part of the code?
Forum members are willing to help, but they cannot waste too much time to build from scratch a sample file for you, so they will ignore this topic until you provide clear info and a sample file with your attempts.
A topic with sample files and a clear description of the problem is more likely to receive a fast answer.
