VBA用户：防止键盘上的COMBOBOX ESCAPE [英] VBA USERFORM: PREVENT COMBOBOX ESCAPE ON KEYDOWN

问题描述

社区

有一种方法可以防止活动组合框在结束时点击向下箭头（或向上箭头） ）的列表。如果有更好的方法来做到这一点（最好用MS标准属性），请分享。

There is a way to prevent an active combobox from losing its focus when hitting the down arrow (or up arrow) when at the end (or start) of a list. If there is a better way to do this (preferably with an MS standard property) please share.

问题：当在ComboBox中的列表末尾，如果你点击向下箭头，它会移动到活动组合框下面的任何控件。反之亦然，在组合框的顶部，击中向上箭头。这是草率和适得其反。 MS Excel 2013。

Problem: When at the end of a list in a ComboBox, if you hit the down arrow it will move you to whatever control is physically below the active combobox. Vice versa for being at the top of a combobox and hitting the up arrow. This is sloppy and counterproductive. MS Excel 2013.

解决方案：为了防止丢失焦点，您可以在userform的ComboBox代码中输入以下内容：

Solution: To prevent this lost focus, in the userform's ComboBox code you can enter the following:

Private Sub Item1_DropDown_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Select Case KeyCode
    Case vbKeyDown
        If Item1_DropDown.ListIndex = Item1_DropDown.ListCount - 1 Then
            Item1_DropDown.ListIndex = Item1_DropDown.ListIndex - 1 'when at the bottom, stay in active combobox
        Else: Item1_DropDown.ListIndex = Item1_DropDown.ListIndex 'if not at the bottom, keep moving down
        End If
    Case vbKeyUp
        If Item1_DropDown.ListIndex = 0 Then 'when at the top, stay in active combobox
            Item1_DropDown.ListIndex = 1
        Else:   Item1_DropDown.ListIndex = Item1_DropDown.ListIndex 'if not at the top, keep moving up
        End If
End Select
      ' where "Item1_DropDown" is the name of my combobox
End Sub

好吧，这就是我能够阻止combobox切换到当在ComboBox列表的底部/顶部时击倒/向上时不同的控制。

Okay, that's how I've been able to prevent the combobox from switching to a different control when hitting down/up when at the bottom/top of a ComboBox list.

有没有人知道一个更清洁的方法来做这个？

Does anyone know of a cleaner way to do this? Maybe a way to do this without using code?

推荐答案

有一个更时尚的方法来做到这一点。它将涉及使用类模块以及使用集合。这些是一对事情，将显着改善你的代码（通过缩短它，并使它复杂化）。

There is an even sleeker way to do this. It will involve the use of a Class Module as well as the use of a Collection. These are a pair of things that will significantly improve your code (by shortening it, and sophisticating it).

本质上，下面的内容是对上述解决方案的演进使用。这里：

Essentially, what lies beneath is an evolved use of the solutions shown above. Here it goes:

步骤1：创建类模块

'clsTask      (this is the 'name' of your Class Module | the name is important!)
Public WithEvents cBox as MSForms.ComboBox    'create a property for your class/object


Public Sub cBox_KeyDown(ByVal KeyCode as MSForms.ReturnInteger, ByVal shift as Integer)

    Select Case KeyCode
        Case vbKeyDown
            if cBox.ListIndex = cBox.ListCount - 1 Then KeyCode = 0
        Case vbKeyUp
            if cBox.ListIndex = 0 Then KeyCode = 0
        Case vbKeyEnd
            cBox.ListIndex = cBox.ListCount - 1
        Case vbKeyHome
            cBox.ListIndex = 0
        Case 46
            cBox.Value = ""
    End Select
'this is just the code from above
End Sub

步骤2：声明集合

'in a standard module            (for Excel 2013, you might need to declare this in a standard module; otherwise, anywhere else may suffice)

Public BoxColl as Collection

步骤＃ 3：初始化Userform

Step #3: Initialize Userform

 '(initialize all desired ComboBoxes in your Userform to use Trap Code (step 1 above))

'userform code

Private Sub Userform_Initialize()

    Dim ctl as Control, ox as clsTask
    Set BoxColl = New Collection
    For Each ctl in Me.Controls
        If TypeOf ctl is MSForms.Label Then            'you could also include an identifying tag here
            Set ox = New clsTask
            Set ox.cBox = ctl
            BoxColl.Add ox
        End if
    Next
'this piece of code sets all of your comboboxes as your defined object/class.
'then it puts each combobox into a collection
'all items in the collection will get the Double Click Event treatment on cue
End Sub

好吧，就是这样。这是有帮助的，如果你有一堆ComboBoxes在你的用户形式，你想应用相同的代码。在这种情况下，如果你想捕获up&下面的按钮在列表的末尾，然后这个代码将工作于每个单一的组合框。

Okay, that's it. This is helpful if you have a bunch of ComboBoxes in your userform which you would like to apply the same code to. In this instance, if you're looking to trap the up & down buttons at the end of the list then this code will work for every single combobox.

请记住，如果你只想让某些组合框有这个功能，你需要添加一个标签给你想要的。然后在userform初始化中，在For ... Each子句中，您将需要在if语句中添加该标记的条件语句。

Keep in mind, if you only want certain comboboxes to have this functionality you will need to add a tag to the ones you want. Then in the userform initialize, within the "For...Each" clause, you will need to add the conditional clause of that tag in the "if" statement.

好吧，把它包起来。这个类模块很重要，因为你不需要为每个&每个组合框你想要的。你只需要做一个代码。

Okay, to wrap this up. This class module is important because you will not need to maintain userform code for each & every combobox you would like. You just need to make one piece of code.

祝你好运！

这篇关于VBA用户：防止键盘上的COMBOBOX ESCAPE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！