0

Is there any alternative toSendKeys to expand drop-down validation list automatically? I would like to expand drop-down validation list after clicking on a cell. The focus of my question is entirely on how to avoid SendKeys method.

Here is a properly working solution using SendKeys:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If HasValidation(Target) Then
        SendKeys "%{DOWN}"
        SendKeys "{NUMLOCK}", True 'Workaround for Numlock turn off bug
    End If
End Sub

Function HasValidation(MyCell As Range) As Boolean
    Dim t: t = Null
    On Error Resume Next
    t = MyCell.Validation.Type
    On Error GoTo 0
    HasValidation = Not IsNull(t)
End Function

Related links:
HasValidation function: https://stackoverflow.com/a/31346246/1903793
NumLock bug: https://stackoverflow.com/a/29551913/1903793

The code above works smoothly without a hitch. I am just biased againstSendKeys for widely reported issues. I suspect that incorporating this solution into larger code might cause unexpected behavior in the future which might be hard to capture.

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • Do you experience issues with this right now? Because I actually think this is quite nice :) – Alex de Jong Feb 28 '19 at 09:56
  • @AlexdeJong No, not any at all. It works smoothly. It's just a matter of caution. I am biased to use SendKeys. I suspect it may lead to issues in the future that will be hard to trap. – Przemyslaw Remin Feb 28 '19 at 15:58

1 Answers1

-1

Your definition of what you wish to achieve leaves a few questions to be answered but perhaps the code below will put you on the right path.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const Trigger As String = "A3:B4"               ' modify to suit
    Const ValCell As String = "H3"                  ' modify as required

    Dim ValType As Long

    If Not Application.Intersect(Target, Range(Trigger)) Is Nothing Then
        Application.EnableEvents = False
        Range(ValCell).Activate

        On Error Resume Next
        ValType = ActiveCell.Validation.Type
        On Error GoTo 0
        If ValType = 3 Then SendKeys "%{DOWN}"

        Application.EnableEvents = True
    End If
End Sub

The above procedure monitors the Trigger range A3:B4, which you can adjust to what you need. If one of the cells in this range (it could be a single cell range) is clicked the dropdown in the cell having the validation, defined as ValCell, expands, provided it exists. The code achieves this by activating the CellVal cell.

Variatus
  • 14,293
  • 2
  • 14
  • 30
  • Sorry, this answer is useless. My question is focused on how to avoid `SendKeys` and not on how to replace `HasValidation` function. You have not proposed replacement for`SendKeys "%{DOWN}"`. On the contrary, you have used that. – Przemyslaw Remin Feb 28 '19 at 15:49
  • Go back to your question and modify it to say what you want. My answer expands the validation drop-down "when a cell is clicked". If this must be accomplished using particular code about which you seem to know nothing except that it shouldn't contain `SendKeys` you owe an explanation why that should be a requirement. – Variatus Mar 01 '19 at 00:51
  • You are absolutely right. I thought I was clear. I refined my question. Thank you! Sorry for misleading you. – Przemyslaw Remin Mar 01 '19 at 07:40
  • The "widely reported issues" amount to the fact that `SendKeys` must be used in a controlled environment and you don't want to make the effort to control yours. Fair enough, but why can't you expand the drop-down by clicking the arrow it has for that purpose? Which cell you want clicked to expand it? How many of them are there on your sheet? – Variatus Mar 01 '19 at 09:25