Option Explicit
Dim Abort as Boolean
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 38 'Up
If TempCombo.ListIndex <= 0 Then KeyCode = 0 'ignore "Up" press if already on the first selection, or if not on a selection
Abort = True
If Not KeyCode = 0 Then ' If on a selection past the first entry
KeyCode = 0
'Manually choose next entry, cancel key press
TempCombo.ListIndex = TempCombo.ListIndex - 1
End If
Me.TempCombo.DropDown
Case 40 'Down
If TempCombo.ListIndex = TempCombo.ListCount - 1 Then Keycode = 0
' This method was from the discussion I linked, prevents "falling off the bottom of the list"
Abort = True
If Not KeyCode = 0 Then ' If on a selection before the last entry
KeyCode = 0
'Manually choose next entry, cancel key press
TempCombo.ListIndex = TempCombo.ListIndex + 1
End If
Me.TempCombo.DropDown
End Select
Abort = False
End Sub
Private Sub TempCombo_Change()
If Abort Then Exit Sub ' Stop Event code if flag set
Abort = True
' sets the flag until finished with commands to prevent changes made by code triggering the event multiple times
' ~~~ Insert Code you want to run for other cases here ~~~
Abort = False
End Sub
1 回答
编辑答案:
现在已经构建了我自己的工作表并使用了这些想法,具有讽刺意味的是
Application.EnableEnable
仅在某些情况下有所帮助,因为Combobox_Change()
事件仍然会在事件被禁用的情况下触发(或者至少似乎是这种情况) . 我发现的基本思想涉及操纵KeyCodes
并设置标志 . 我下面的例子涉及使用一个名为TempCombo
的ComboBox
并在VBA中的TempCombo_KeyDown()
事件之后运行代码(为了示例目的,我已经修剪了我的东西):我使用
Abort
变量作为TempCombo_Change()
中的标志来防止事件代码的多次触发,并允许键不更改链接单元格的文本结果,从而阻止我的动态范围更新 . 确保两个子程序都在ComboBox
所在的工作表上!所以这是我为此所做的一个骷髅,如果有人发现问题让我知道,但我希望这可以帮助别人 .