vba Excel 组合框问题

Excel combo box problem

提问by Tmdean

I have a form in Excel with a combo box control. I want the values to be filled from a database table when the combo box is opened using what has already been typed in as a LIKE criteria. This is the code I have so far for the DropButtonClick event to achieve this.

我在 Excel 中有一个带有组合框控件的表单。我希望在使用已作为 LIKE 条件输入的内容打开组合框时从数据库表中填充值。这是我迄今为止为 DropButtonClick 事件实现的代码。

Private Sub cboVariety_DropButtonClick()
    Static search_text As String
    Static is_open As Boolean
    Dim rs As New Recordset

    If is_open Then
        is_open = False
        Exit Sub
    End If
    is_open = True

    If search_text = cboVariety Then Exit Sub
    search_text = cboVariety

    cboVariety.Clear
    cboVariety.AddItem search_text
    If Len(search_text) > 2 Then
        rs.Open _
            "SELECT Name FROM tbl_Varieties " & _
            "WHERE Name LIKE '%" & search_text & "%' " & _
            "ORDER BY Name", connect_string, adOpenStatic
        Do Until rs.EOF
            If rs!Name <> search_text Then cboVariety.AddItem rs!Name
            rs.MoveNext
        Loop
        rs.Close
    End If
End Sub

The problem is that the DropButtonClick event fires both when the combo box is opened and when it is closed. If this sub executes when the combo box is closing, the code that clears the combo box causes the user's selection to be erased.

问题是 DropButtonClick 事件在组合框打开和关闭时都会触发。如果这个子在组合框关闭时执行，清除组合框的代码会导致用户的选择被删除。

I'm trying to tell when the box is closed using the is_open variable, which alternates between true and false each time the event sub is executed. This seems like a brittle solution to the problem. Is there a better way?

我正在尝试使用 is_open 变量判断框何时关闭，每次执行事件 sub 时，该变量在 true 和 false 之间交替。这似乎是解决问题的脆弱方法。有没有更好的办法？

采纳答案by Tmdean

I found a simple way to solve this. It doesn't seem like it should work, but if I just reassign the value of the combo box after rebuilding the list, it doesn't discard the value that is selected.

我找到了一个简单的方法来解决这个问题。它似乎不应该工作，但如果我在重建列表后重新分配组合框的值，它不会丢弃所选的值。

Private Sub cboVariety_DropButtonClick()
    Static search_text As String
    Dim rs As New Recordset

    If search_text = cboVariety Then Exit Sub
    search_text = cboVariety

    cboVariety.Clear
    If Len(search_text) > 2 Then
        rs.Open _
            "SELECT Name FROM tbl_Varieties " & _
            "WHERE Name LIKE '%" & search_text & "%' " & _
            "ORDER BY Name", connect_string, adOpenStatic
        Do Until rs.EOF
            cboVariety.AddItem rs!Name
            rs.MoveNext
        Loop
        rs.Close
    End If

    '' Reassign cboVariety in case this event was triggered by combo close
    cboVariety = search_text
End Sub

回答by Ben McCormack

You are on the right track by using the is_openboolean to track the state of the combo box, but what you really want to track is the state of "should I re-populate the combo box with database data?"

通过使用is_open布尔值来跟踪组合框的状态，您走上了正确的轨道，但您真正想要跟踪的是“我应该用数据库数据重新填充组合框吗？”的状态。

When do you want the list box populated? Currently, you want the list box to be populated every time the user clicks the drop-down box (not taking into account your is_openstate variable). Is this really what you want?

您希望何时填充列表框？目前，您希望每次用户单击下拉框时都填充列表框（不考虑您的is_open状态变量）。这真的是你想要的吗？

I would imagine that what you really want is to have the combo box only update after something else changes. Perhaps you only want the drop down list to update when the form first opens. Maybe you only want the data to change when the text in a search box changes. If this is the case, you need to base your logic on the state of when you actually want to perform the update.

我想你真正想要的是让组合框只在其他更改后更新。也许您只想在表单首次打开时更新下拉列表。也许您只想在搜索框中的文本更改时更改数据。如果是这种情况，您需要根据实际想要执行更新的状态来构建逻辑。

For example, let's say you want to update the combo box only if the text in a search box changes. I'm not looking at Excel at the moment, but let's pretend you have a text box called txtSearchwith a Textproperty. I'd start by adding a module or class level variable to maintain the state of the previous text entry:

例如，假设您只想在搜索框中的文本更改时更新组合框。我目前不看 Excel，但让我们假设您有一个txtSearch带有Text属性的文本框。我首先添加一个模块或类级别的变量来维护前一个文本条目的状态：

Private mPreviousSearchText As String

Then I'd update my event code like so:

然后我会像这样更新我的事件代码：

Private Sub cboVariety_DropButtonClick()                                      
    Dim rs As New Recordset                    
    Dim search_text As String

              search_text = txtSearch.Text

    If mPreviousSearchText = search_text Then                      
       'The current search matches the previous search,'
       'so we do not need to perform the update.'
        Exit Sub                    
    End If                                                    

    cboVariety.Clear                    
    cboVariety.AddItem search_text                    
    If Len(search_text) > 2 Then                    
        rs.Open _                    
            "SELECT Name FROM tbl_Varieties " & _
            "WHERE Name LIKE '%" & search_text & "%' " & _
            "ORDER BY Name", connect_string, adOpenStatic
        Do Until rs.EOF                    
            If rs!Name <> search_text Then cboVariety.AddItem rs!Name
            rs.MoveNext                    
        Loop                    
        rs.Close                                
    End If             
    'Set the previousSearchText var to be the search_text so that it does'
    'not run unless the value of my text box changes.'
    mPreviousSearchText = search_text 
End Sub

The entire point is to establish when you actually want to perform the updateand find out a way to tie your logic decision to the state associated with when you want to perform the action, which is only coincidentally related to the user clicking on the drop-down box.

重点是确定您真正想要执行更新的时间，并找到一种方法将您的逻辑决策与您想要执行操作时关联的状态联系起来，这只是巧合地与用户单击下拉菜单有关 -下盒。

回答by Jordi

This worked for me, instead of assigning the value, i assign the ListIndexproperty.

这对我有用，我没有分配值，而是分配了ListIndex属性。

index = cb.ListIndex

cb.Clear

while condition
    cb.AddItem item
Wend

If index < cbLinia.ListCount Then
    cb.ListIndex = index
Else
    cb.ListIndex = -1
End If

回答by Dr. belisarius

Use GotFocus() instead.

请改用 GotFocus()。

 Private Sub ComboBox1_GotFocus()
    MsgBox "caca"
 End Sub

Triggers only when the combo get focus.

仅在组合获得焦点时触发。

HTH

HTH