Forum Discussion

Jeremy1565's avatar
Jeremy1565
Copper Contributor
Sep 20, 2022
Solved

VBA Alt + Down Macro

I'm creating a table that has drop down lists in multiple columns, and would like a VBA code to press Alt + down when any cell in those certain columns are selected, rather than clicking the cell and then the drop down menu.

 

I've got a code to open the drop down menu when a specific cell is selected, but I would have to copy it hundreds of times for every cell

 

Thank you for your help

excel
Macros and VBA
  • JMB17's avatar
    JMB17
    Sep 20, 2022

    Jeremy1565 Assuming you are using a selection_change event, you could test the target range to see if it has an in cell dropdown (%{DOWN} causes my number and caps locks to toggle, but %{UP} appears to work okay) - it should work for any cell on the worksheet. 

     

    If you wanted to restrict it to specific columns, you could add another test to intersect target with those ranges (say columns E, G, and I):

    If Intersect(Target, Me.Range("E:E, G:G, I:I")) Is Nothing Then Exit Sub

     

     

     

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
     On Error GoTo ErrHandler
     If Target.Cells.CountLarge > 1 Then Exit Sub
     
     If Target.Validation.InCellDropdown Then
          Application.SendKeys "%{UP}"
     End If
 
ExitProc:
     Exit Sub
     
ErrHandler:
     Resume ExitProc
     
End Sub

     

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Sep 20, 2022

    Jeremy1565 Assuming you are using a selection_change event, you could test the target range to see if it has an in cell dropdown (%{DOWN} causes my number and caps locks to toggle, but %{UP} appears to work okay) - it should work for any cell on the worksheet. 

     

    If you wanted to restrict it to specific columns, you could add another test to intersect target with those ranges (say columns E, G, and I):

    If Intersect(Target, Me.Range("E:E, G:G, I:I")) Is Nothing Then Exit Sub

     

     

     

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
     On Error GoTo ErrHandler
     If Target.Cells.CountLarge > 1 Then Exit Sub
     
     If Target.Validation.InCellDropdown Then
          Application.SendKeys "%{UP}"
     End If
 
ExitProc:
     Exit Sub
     
ErrHandler:
     Resume ExitProc
     
End Sub

     

Resources