Select the next item in a validation list VBA-CodePudding

I'm creating a simple form in excel that will run in a touch device. In some part of the form i have created a dropdown validation list. I need to go to the next and previous item in the validation list with a button.

The only way thay i found is using Application.Sendkeys to acces to the dropdown but is not working. Any thoughts?

sub Next_item click()
    
ActiveSheet.Range(cell_rng.Text).Select
Application.SendKeys ("%{Down}")
Application.SendKeys ("{Down}{Enter}")

End Sub

CodePudding user response：

You might consider going to the source of the data validation and grabbing the next item rather than trying to manipulate the in-cell dropdown directly. For instance, if you data validation is a comma-separated list

you could use code like this to get the next value:

Public Sub NextItem()
    
    Dim dv As Validation
    Dim vaSplit As Variant
    Dim i As Long
        
    'Formula1 contains the comma sep list of values
    Set dv = ActiveCell.Validation
    vaSplit = Split(dv.Formula1, ",")
    
    For i = LBound(vaSplit) To UBound(vaSplit)
        'if you're at the currently selected one
        If vaSplit(i) = ActiveCell.Value Then
            'but not at the last one
            If i < UBound(vaSplit) Then
                'select the next one
                ActiveCell.Value = vaSplit(i   1)
                Exit For
            End If
        End If
    Next i
    
End Sub

And if your list comes from a simple range

you could use almost the same code

Public Sub NextItemRange()
    
    Dim dv As Validation
    Dim vaSplit As Variant
    Dim i As Long
    
    Set dv = ActiveCell.Validation
    
    vaSplit = Range(dv.Formula1).Value
    
    For i = LBound(vaSplit, 1) To UBound(vaSplit, 1)
        If vaSplit(i, 1) = ActiveCell.Value Then
            If i < UBound(vaSplit, 1) Then
                ActiveCell.Value = vaSplit(i   1, 1)
                Exit For
            End If
        End If
    Next i
    
End Sub

Dynamic ranges or tables might throw a wrench into it, but hopefully it's a start.