Double Clicking Through a List

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.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
    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?

Posted in Uncategorized

7 thoughts on “Double Clicking Through a List

  1. 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.

  2. 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.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
       
        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
  3. After replacing the occurrences of “>” with “>” this works flawlessly.

    Nice one with the “Application.International(xlListSeparator)”, much sleeker than the method I posted.

  4. 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.

  5. 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.

  6. 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”

  7. “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”

Leave a Reply

Your email address will not be published. Required fields are marked *