Excel选中单元格自动显示下拉列表及NumLock异常修复咨询

方案一：修复原代码的NumLock异常问题 #

针对原代码中SendKeys导致NumLock频繁关闭的问题，可通过记录并恢复NumLock状态解决：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lDVType As XlDVType
    Dim numLockState As Boolean
    
    ' 记录当前NumLock状态：通过Shift+NumLock切换后获取状态，再还原初始状态
    numLockState = Application.SendKeys("+{NUMLOCK}", True)
    Application.SendKeys("+{NUMLOCK}", True)
    
    If Target.Cells.CountLarge = 1 Then
        On Error Resume Next
        lDVType = Target.Validation.Type
        On Error GoTo 0
        
        If lDVType = xlValidateList Then
            SendKeys "%{down}"
            ' 恢复NumLock状态
            If numLockState <> Application.SendKeys("+{NUMLOCK}", True) Then
                Application.SendKeys("{NUMLOCK}", True)
            End If
        End If
    End If
End Sub

核心逻辑：先保存当前NumLock状态，执行SendKeys操作后对比状态，若发生变化则强制还原。

方案二：使用Windows API替代SendKeys（更稳定） #

SendKeys本身存在兼容性和副作用问题，推荐用Windows API直接触发下拉列表展开，彻底避免NumLock异常：

步骤1：声明API函数 #

将以下代码放在工作表模块的通用声明区域（代码顶部）：

' 兼容64位Excel的API声明，32位Excel可去掉PtrSafe，将LongPtr改为Long
Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As LongPtr

Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _
    (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, _
    lParam As Any) As LongPtr

Private Const CB_SHOWDROPDOWN As Long = &H14F

步骤2：修改SelectionChange事件代码 #

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lDVType As XlDVType
    Dim hwndCombo As LongPtr
    
    If Target.Cells.CountLarge = 1 Then
        On Error Resume Next
        lDVType = Target.Validation.Type
        On Error GoTo 0
        
        If lDVType = xlValidateList Then
            ' 定位Excel编辑栏中的下拉控件句柄
            hwndCombo = FindWindowEx(Application.hwnd, 0, "EDTBX", vbNullString)
            hwndCombo = FindWindowEx(hwndCombo, 0, "COMBOBOX", vbNullString)
            
            ' 发送展开下拉列表的消息
            If hwndCombo <> 0 Then
                SendMessage hwndCombo, CB_SHOWDROPDOWN, 1, 0
            End If
        End If
    End If
End Sub

核心逻辑：通过Windows API定位Excel窗口内的下拉控件，直接发送展开指令，完全不依赖SendKeys，既消除NumLock副作用，又提升操作稳定性。

内容的提问来源于stack exchange，提问作者ThomasS