I have a cell with data validation. It’s set as an in-cell dropdown list and contains two items: Yes and No. I want to make it so that I can double click on that cell to toggle between yes and no. But wait, that’s not good enough. What about longer lists? Yes, I want something that will iterate through all the items on a data validation list by double clicking. Dare I dream.
Here’s my first go at it. I figure it’s going to need some work, like what happens when the user double clicks on something that’s not a range, but it’s a start. I’ve basically handled two types of lists: the kind where you hard code values separated by commas (international issue here?), and the the range reference. Oh, and I need to test named ranges, but I think they’ll work.
Dim dv As Validation
Dim sDv1 As String
Dim vaList As Variant
Dim i As Long
Dim vOldValue As Variant
On Error Resume Next
Set dv = Target.Validation
sDv1 = dv.Formula1
On Error GoTo 0
If Len(sDv1) > 0 Then ‘only if the cell has dv
If dv.Type = xlValidateList Then
Cancel = True ‘don’t do the default action
vOldValue = Target.Value
vaList = GetValidList(dv.Formula1) ‘return single dim array
For i = LBound(vaList) To UBound(vaList)
If vaList(i) = Target.Value Then
If i = UBound(vaList) Then
Target.Value = vaList(LBound(vaList))
Else
Target.Value = vaList(i + 1)
End If
Exit For
End If
Next i
If Target.Value = vOldValue Then ‘if cell was blank
Target.Value = vaList(LBound(vaList)) ‘go to first item
End If
End If
End If
End Sub
Private Function GetValidList(sForm As String) As Variant
Dim vArr As Variant
Dim vaReturn As Variant
Dim i As Long
Dim bIsRange As Boolean
On Error Resume Next
vArr = Evaluate(sForm) ‘for range reference
On Error GoTo 0
If IsError(vArr) Then ‘for csv list
vArr = Split(sForm, “,”)
bIsRange = False
Else
bIsRange = True
End If
If bIsRange Then ‘conver to single dim array
ReDim vaReturn(0 To UBound(vArr, 1) – 1)
For i = LBound(vArr, 1) To UBound(vArr, 1)
vaReturn(i – 1) = vArr(i, 1)
Next i
Else
vaReturn = vArr
End If
GetValidList = vaReturn
End Function
This code is in the sheet’s class module (Sheet1 in my case). Test it out if you like. Let me know if you see any errors or better ways.
P.S. Why is Target.Validation always something (that is, Not Nothing) even if the cell doesn’t have validation?
Hi Dick,
great piece of code you have there. I can confirm the international issue you suspected; I’m using Excel 2003 on a machine set up with Norwegian settings in the control panel. In Norwegian we are using “;” as list separator, not the regular comma. This yields some problems. If I define the list with “;” the Excel data validation works fine, but your code interprets the validation formula as just one entry. The other way around, defining the list with “,” yields the opposite results; Excel interprets the validation as a single item while your code treats it as several entries. This behavior is logical an actually expected if you think about it.
In this thread a method is described for obtaining the list separator. Using this method in conjunction with your code seems to work well on my system (Win2k).
You could also read out the sList value from “HKEY_CURRENT_USERControl PanelInternational” in the registry.
This variation of your code uses the MATCH function instead of looping through the items.
If the users double clicks anything other than a range I don’t believe the worksheet event will be fired.
Dim vaList As Variant
Dim lngIndex As Long
Dim vOldValue As Variant
On Error GoTo ErrDoubleClick
With Target
If .Validation.Type = xlValidateList Then
If Len(Target.Validation.Formula1) > 0 Then ‘only if the cell has dv
Cancel = True ‘don’t do the default action
vOldValue = Target.Value
vaList = GetValidList(.Validation.Formula1) ‘return single dim array
If Len(vOldValue) > 0 Then
lngIndex = Application.WorksheetFunction.Match(vOldValue, vaList, 0)
If lngIndex > UBound(vaList) Then lngIndex = LBound(vaList) ‘ return first item
End If
Target.Value = vaList(lngIndex)
End If
End If
End With
ErrDoubleClick:
Exit Sub
End Sub
Private Function GetValidList(sForm As String) As Variant
Dim vArr As Variant
Dim vaReturn As Variant
Dim lngIndex As Long
If Left(sForm, 1) = “=” Then
‘ range/named range
vArr = Evaluate(sForm)
ReDim vaReturn(0 To UBound(vArr) – 1)
For lngIndex = LBound(vaReturn) To UBound(vaReturn)
vaReturn(lngIndex) = vArr(lngIndex + 1, 1)
Next
Else
‘for csv list
vaReturn = Split(sForm, Application.International(xlListSeparator))
End If
GetValidList = vaReturn
End Function
After replacing the occurrences of “>” with “>” this works flawlessly.
Nice one with the “Application.International(xlListSeparator)”, much sleeker than the method I posted.
Very Nice, Andy. (You too Dick!)I’m going to drop this in my code today.
Just one wrinkle for me: when I used a validation of True,False with direct entry, the “oldValue” variable was type boolean, and the match function failed against the “VaList” values of “True” and “False”. I modified “oldValue” to type String, and it worked, but I’m not sure that this will work in all cases.
Great idea Dick, and nice amendment from Andy.
this is very useful and something I will definitely make use of.
Perfect for selecting/switching VAT codes on financial sheets.
Since this is code critic, I’d just like to point out you cannot use “Not Nothing” in code. It would result in a compilation error.
‘ This will not compile
If obj Is Not Nothing Then MsgBox “Hi”
‘ This will compile
If Not obj Is Nothing Then MsgBox “Hi”
“P.S. Why is Target.Validation always something (that is, Not Nothing) even if the cell doesn’t have validation?”
All cells in Excel are validated but with a validation type called “Any Value”