1

I have an Excel sheet with lot of data entry fields. Some have dropdowns and I ise Alt+Down arrow to make selections. But for a cell not containing any Data Validation dropdown shows me the recently entered values in the same column if I press Alt+Down arrow. Any way to stop this? enter image description here

I don't want this suggestion box if I press Alt + Down in cell B6. Turning off Autofill didn't help.

Kamlesh Kishor
  • 121
  • 1
  • 15

2 Answers2

2

I don't know how you could do this for blank cells only, but you can disable the hotkey using VBA code. 

Private Sub Workbook_Disable()
    Application.OnKey "+^{DOWN}", ""
End Sub

Private Sub Workbook_Enable()
    Application.OnKey "+^{DOWN}"
End Sub

https://msdn.microsoft.com/en-us/library/office/ff197461.aspx

Jorvik77
  • 342
  • 1
  • 15
1

Knowing that alt + up also opens the drop-down list, it's possible to do:

Private Sub Setup()
    Application.OnKey "%{DOWN}", "HandleAltDown"
End Sub

Private Sub HandleAltDown()
    If HasValidation(Application.ActiveCell) Then
        Application.SendKeys "%{UP}"
    End If
End Sub

Function HasValidation(cell As range) As Boolean
    Dim t: t = Null

    On Error Resume Next
    t = cell.Validation.Type
    On Error GoTo 0

    HasValidation = Not IsNull(t)
End Function

It works pretty well. HasValidation was taken from this answer: https://stackoverflow.com/a/31346246/2862952

mathiasfk
  • 1,278
  • 1
  • 19
  • 38