如何在不选择列出的项目的情况下使用组合键下拉事件 [英] 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?

推荐答案

已编辑答案：

Edited Answer:

讽刺的是 Application.EnableEnable 仅在某些情况下有帮助，因为 Combobox_Change（）事件仍然发生与事件禁用（或似乎是这种情况，至少）。我发现涉及操作 KeyCodes 和设置标志的基本想法。我的例子涉及使用 ComboBox 调用 TempCombo 并运行代码 TempCombo_KeyDown / code>事件在VBA内的表（我已修剪我的东西下来为例子的目的）：

Now having built my own sheet and worked with these ideas, ironically Application.EnableEnable 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

我使用 Abort 标志在 TempCombo_Change（）中以防止事件代码的多次触发，并允许键不更改链接单元格的文本结果，从而防止我的动态范围更新。确保两个子程序都位于 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之间的差异，对于
实例 Me.EnableEvents UserForm 需要为
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!

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