如何在不选择列出的项目的情况下使用组合框 keydown 事件 [英] How use the combobox keydown event without selecting the listed item

问题描述

我的 Excel VBA 项目中有一个组合框，它使用一系列单元格作为项目列表.我在其中使用了一个过滤器，这样每当输入一个值时，列表就会缩小到包含该字符串的项目，并显示下拉列表.但是，问题出现了，显示下拉菜单时，无法使用导航键在项目内滚动.一旦按下向下键，下拉列表将再次被过滤.

I have in my Excel VBA project, a combobox which uses a range of cells as it's list of items. I have used a filter in it, so that whenever a value is entered, the list shrinks down to the items containing the string, and dropdown list is shown. However, the problem appears, when the dropdown is shown, the navigation keys can't be used to scroll within the items. As soon as the down key is pressed the dropdown list will be filtered again.

我猜这是因为在关注项目的同时按下向下键也在选择它.因此，combobox_change 事件会被自动调用.

I guess its happening because the down key while focusing on the items, is also selecting it. Hence, the combobox_change event is called automatically.

有没有办法让我可以停止 keydown 事件自动选择一个项目，但只能滚动它们?

Is there a way so that I can stop the keydown event automatically selecting an item, but only scroll through them?

推荐答案

编辑答案:

现在已经构建了我自己的工作表并使用了这些想法，具有讽刺意味的是 Application.EnableEvents 仅在某些情况下有帮助，因为 Combobox_Change() 事件仍然在禁用事件的情况下触发(或者至少看起来是这样).我发现的基本思想涉及操作 KeyCodes 和设置标志.我下面的示例涉及使用一个名为 TempCombo 的 ComboBox 并在 VBA 中的 TempCombo_KeyDown() 事件之后运行代码(我已经修剪了我的为了示例目的而放下):

Now having built my own sheet and worked with these ideas, ironically Application.EnableEvents only helps in certain situations because the Combobox_Change() event still fires with events disabled (or seems to be the case, at least). The basic idea that I found involved manipulating the KeyCodes and setting flags. My example below involves using a ComboBox called TempCombo and running code after the TempCombo_KeyDown() event within the VBA for the sheet (I have trimmed my stuff down for example purposes):

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
 

我在 TempCombo_Change() 中使用了 Abort 变量作为标志，以防止多次触发事件代码，并允许按键不更改文本结果链接的单元格，阻止我的动态范围更新.确保两个子程序都在 ComboBox 所在的工作表上！

I used the Abort variable as a flag in the TempCombo_Change() to prevent both multiple firings of the event code, and allow keys to not change the text result of the linked cell, preventing my dynamic range from updating. Make sure both subroutines are on the sheet where the ComboBox is!

这就是我为此所做的工作的骨架，如果有人发现问题，请告诉我，但我希望这可以帮助某人.

So that is a skeleton of what I did for this, if someone finds a problem let me know, but I hope this can help someone.

旧答案

我不确定这会有多大帮助，如果我有我只会将此作为评论提交，因为这确实没有有资格作为答案.但是我有同样的问题并偶然发现试图回答这个问题的线程.我希望其中一个代码Microsoft 的帮助页面之一:http://answers.microsoft.com/en-us/office/forum/office_2007-customize/disable-userform-combobox-change-event-when-arrow/598b44a1-dcda-4a2c-8e12-2b84762f98ae?db=5

I'm not sure how much this will truly help, and if I had the reputation I would just submit this as a comment because this really doesn't qualify as an answer. However I had the same question and stumbled across a thread trying to answer this. I hope that one of the codes on one of Microsoft's help pages: http://answers.microsoft.com/en-us/office/forum/office_2007-customize/disable-userform-combobox-change-event-when-arrow/598b44a1-dcda-4a2c-8e12-2b84762f98ae?db=5

似乎在查看向上和向下箭头的 KeyDown 事件并与 ComboBox_Change 事件配对将允许您隔离然后防止组合框在您向上和向下按下时更新导航列表.

It seems that looking at the KeyDown events for the up and down arrows and pairing with the ComboBox_Change events would allow you to isolate them and then prevent the combobox from updating when you press up and down to navigate the list.

我建议查看 OP 的后续帖子，这太不可思议了信息丰富，帮助我适应我自己的情况.还要注意 UserForm 和一般 Excel VBAcode 之间的区别，对于UserForm 的实例 Me.EnableEvents 需要是Application.EnableEvents 适用于一般excel！

I recommend looking through the OP's follow up post, it is incredibly informative and helped me adapt to my own case. Also note differences between UserForm and general Excel VBAcode, for instance Me.EnableEvents for UserForm would need to be Application.EnableEvents for general excel!

我知道这现在已经过时了，但如果这对任何人有帮助，祝你好运！

I know this is old now, but in case this helps anyone, good luck!

这篇关于如何在不选择列出的项目的情况下使用组合框 keydown 事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！