How to Trigger In-Cell dropdown list in Excel Cell on Alt + Down Arrow by VBA

Question

I am using Excel 2007. I have coded a userform which functions like Excel 2010 Autofilter dropdown and assigned alt+down shortcut for this userform load macro. Now I am trying to show the In-Cell dropdown aka the default drop in a cell instead the userform if the cell is not in autofilter range like below.

Sub AUTOFILTEREXCEL2010VERSION()
If ActiveSheet.AutoFilterMode Then
  Load AutoFilterfrm
  AutoFilterfrm.Show
Else
  'Application.OnKey "%{DOWN}", "" 'Tried this alternative also
  Application.OnKey "%{DOWN}"
  'Application.SendKeys "%{DOWN}" 'Tried this alternative also
  SendKeys "%{DOWN}"
  Application.OnKey "%{DOWN}", "AUTOFILTEREXCEL2010VERSION"
End If
End Sub

Thanks to Kamlesh Kishor for the image.

The problem is the above code does not display the in-Cell dropdown list like the above image i.e. if ActiveSheet.AutoFilterMode is False.

Pᴇʜ Pᴇʜ · Accepted Answer · 2021-04-07T08:36:00

So first the Alt+Down is the same as right click into a cell and selecting "Pick from drop-down list...". This context menu button is assigned to the shortcut Alt+Down.

So instead of removing and reapplying the shortcut and going with a crappy SendKeys that comes with a lot of issues, just do the following and run the context menu directly:

Public Sub AUTOFILTEREXCEL2010VERSION()
    If ActiveSheet.AutoFilterMode Then
        Load AutoFilterfrm
        AutoFilterfrm.Show
    Else
        Application.CommandBars("Cell").FindControl(ID:=1966).Execute 'execute the kontext menu item
        Application.OnKey "%{DOWN}", "AUTOFILTEREXCEL2010VERSION"
    End If
End Sub

Instead of using the ID:=1966 of the menu item (which makes it work in different languages) you could also use

Application.CommandBars("Cell").Controls("Pic&k From Drop-down List…").Execute

but that would only work for english Excel versions then.

Note the difference between thow 2 lines below:

Application.OnKey "%{DOWN}"      ' re-assigns the shortcut to its original
Application.OnKey "%{DOWN}", ""  ' removes the shortcut completely (does nothing anymore)

How to Trigger In-Cell dropdown list in Excel Cell on Alt + Down Arrow by VBA

1 Answers