未添加到组合框VB​​A的项目 [英] Items not adding to combobox VBA

问题描述

我使用Excel VBA将来自另一个工作表的单元格行的字符串放置到组合框下拉列表中。当用户在组合框中键入时，下拉列表应该过滤结果，只显示包含与在组合框中键入的字符相同的字符的结果。然而，我不能得到的代码，使这种行为发生。该代码似乎只抓取数据表中每个字符串的第一个字符，并且不匹配任何字符串任何位置的字符。

工作簿打开时：

  Public Sub Workbook_Open（）
 InitnewCmb 
 End Sub 
  
 
 
 
 newMdl：
 
 
 
这部分都可以使用：
  public newCol As Collection 
 public indNewCol As long 
 public lastColumn As Long 
 
 public newCargoNum As Long 
 
 
 
 Public Sub InitnewCmb（）
'初始化combobox 
 lastColumn = Database.Cells.SpecialCells（xlCellTypeLastCell）.Column 
 
设置newCol =新集合
 newCargoNum = 0 
 With newCol 
对于indNewCol = 2到lastColumn 
 
。添加Database.Cells（2，indNewCol）.Value 
 '取每个单元格的值，一个字符串并添加到字符串集合
 
 newCargoNum = newCargoNum + 1 
 
下一个indNewCol 
 
结束于
  
这里是事情失控的地方。 FilinterewCmb现在在InitnewCmb内部调用
  FilternewCmb
 
 
 End Sub 
 
 Public Sub FilternewCmb（newFilter As String）
 Dim l As Long 
 
 
 
 
对于l = 1到newCargoNum 
 
 
如果InStr（1，newCol.Item（1），newFilter，vbTextCompare）<> 0 Then 
'如果输入的字符与集合中任何字符串中的字符匹配
 Tool.newCmb.AddItem newCol.Item（l）
 
 
'保留这些字符串下拉
结束如果
 
下一个l 
 
结束子
  
 
 b $ b 
有人可以指出我正确的方向为什么过滤不工作？最后，一旦在下拉列表中选择了一个项目，我想要该项目填充组合框，并且下拉菜单也消失，这应该很容易。
 
 
 
谢谢。 / p> 
 
解决方案
此设置将在您输入时过滤一个ComboBox下拉列表
 
 
在Sheet1上创建一个新的ComboBox（ActiveX Control），如下图所示，名为ComboBox1
 
 
 
将此代码添加到Sheet1 VBA模块：
 选项显式
 
私有cLst作为变式
 
私有子工作表-SelectionChange1（ByVal目标作为范围）
 cLst = Sheet1.UsedRange.Columns（1）
 Sheet1.ComboBox1.List = cLst 
 Sheet1.ComboBox1.ListIndex = -1 
 End Sub 
 
 Private Sub ComboBox1_Change（）
 filterComboList Sheet1.ComboBox1，cLst 
 End Sub 
 
 Private Sub ComboBox1_KeyPress（ByVal KeyAscii As MSForms.ReturnInteger）
 Sheet1.ComboBox1。 DropDown 
 End Sub 
 
 Private Sub ComboBox1_GotFocus（）'或_MouseDown（）
 Sheet1.ComboBox1.DropDown 
 End Sub 
 
 Public Sub filterComboList（ByRef cmb As ComboBox，ByRef dLst As Variant）
 Dim itm As Variant，lst As String，sel As String 
 
 Application.EnableEvents = False 
使用cmb 
 sel = .Value 
如果IsEmpty（cLst）then cLst = Sheet1.UsedRange.Columns（1）
对于每个itm在cLst 
如果Len（itm）> 0然后如果InStr（1，itm，sel，1）then lst = lst& itm& || 
 Next 
如果Len（lst）> 0 Then .List = Split（Left（lst，Len（lst） -  2），||）Else .List = dLst 
 End With 
 Application.EnableEvents = True 
 End Sub 
  
 
 
 
 
 
 
  
 
 
 
更改Sheet1上的单元格选择以刷新下拉列表
 
I am using Excel VBA to place strings from another sheet's row of cells into a combobox dropdown. When a user types into the combobox, the dropdown should be filtering results to only results that contain the same character(s) as typed in the combobox. However, I cannot get the code to make this behavior happen. The code only seems to grab the first character from each string in the data sheet and not match a character at any position of any string.

When the workbook is open:
Public Sub Workbook_Open()
InitnewCmb
End Sub
newMdl:

This part all works:
Public newCol As Collection
Public indNewCol As Long
Public lastColumn As Long

Public newCargoNum As Long



Public Sub InitnewCmb()
'Initialize combobox
lastColumn = Database.Cells.SpecialCells(xlCellTypeLastCell).Column

    Set newCol = New Collection
    newCargoNum = 0
    With newCol
        For indNewCol = 2 To lastColumn

            .Add Database.Cells(2, indNewCol).Value
        'Take the value of each cell, a string and add to the collection of strings

        newCargoNum = newCargoNum + 1

        Next indNewCol

    End With
Here's where things get out of hand. FilternewCmb is called now inside InitnewCmb        
FilternewCmb ""


End Sub

Public Sub FilternewCmb(newFilter As String)
Dim l As Long




    For l = 1 To newCargoNum


        If InStr(1, newCol.Item(l), newFilter, vbTextCompare) <> 0 Then
            'If entered character matches a character in any string in collection
            Tool.newCmb.AddItem newCol.Item(l)


            'keep these strings in dropdown
        End If

    Next l

End Sub
Can someone please point me in the right direction on why the filtering is not working? Finally, once an item is selected in the dropdown, I want that item to populate the combobox and have the dropdown disappear too, which should be easy.

Thank you.
解决方案
This setup will filter a ComboBox drop-down list as you type

Create a new ComboBox on Sheet1 (ActiveX Control), as in the image bellow, named "ComboBox1"

Add this code to Sheet1 VBA module:
Option Explicit

Private cLst As Variant

Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
    cLst = Sheet1.UsedRange.Columns(1)
    Sheet1.ComboBox1.List = cLst
    Sheet1.ComboBox1.ListIndex = -1
End Sub

Private Sub ComboBox1_Change()
    filterComboList Sheet1.ComboBox1, cLst
End Sub

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Sheet1.ComboBox1.DropDown
End Sub

Private Sub ComboBox1_GotFocus()   'or _MouseDown()
    Sheet1.ComboBox1.DropDown
End Sub

Public Sub filterComboList(ByRef cmb As ComboBox, ByRef dLst As Variant)
    Dim itm As Variant, lst As String, sel As String

    Application.EnableEvents = False
    With cmb
        sel = .Value
        If IsEmpty(cLst) Then cLst = Sheet1.UsedRange.Columns(1)
        For Each itm In cLst
            If Len(itm) > 0 Then If InStr(1, itm, sel, 1) Then lst = lst & itm & "||"
        Next
        If Len(lst) > 0 Then .List = Split(Left(lst, Len(lst) - 2), "||") Else .List = dLst
    End With
    Application.EnableEvents = True
End Sub






Change cell selection on Sheet1 to refresh the drop-down list

                        
这篇关于未添加到组合框VB​​A的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！