时间戳

首页 > 解决方案 > 在 VBA for Excel 中创建类型搜索敏感组合框

excel - 在 VBA for Excel 中创建类型搜索敏感组合框

问题描述

我是 VBA 新手。一般来说，我会使用Excel函数来处理我自己的内部分析等。但是，在一个资源稀缺的新创业公司，我需要创建一个用户，用户可以从中搜索列表职业，当找到时，它应该在文本框中显示附加到该职业的参数列表，除了输入要搜索的新职业之外，不能更改这些值。

我从我的电子表格开始（4610 行数据，每行旁边有 11 列数据）。我尝试创建的是一个 alpha 敏感类型搜索组合框，用户应该开始输入他们正在搜索的职业，它应该列出 8 个职业作为用户类型，然后他可以选择正确的一个，然后显示另一个用户表单上的参数。

我从用户表单开始，从工具中选择了 ComboBox 和其他应该显示数据的文本框。

我进入 ComboBox 的 Properties 并将 MatchEntry 设置为 1 - fmMatchEntryComplete 和 ControlTipText开始输入您要查找的职业并将 ListRows 设置为 8。

我在 StackOverflow 上搜索了指导并调整了以下线程中显示的代码？

这是我的组合框代码：

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim x, dict
Dim i As Long
Dim str As String

Set ws = Sheets("Occupations")
Set Rng = ws.Range(Cell1:="C2")


x = ws.Range("C2").Value
Set dict = CreateObject("scripting.dictionary")
str = Me.ComboBox1.Value
If str <> "" Then
    For i = 1 To UBound(x, 1)
        If InStr(LCase(x(i, 1)), LCase(str)) > 0 Then
            dict.Item(x(i, 1)) = ""
            End If
        Next i
        Me.ComboBox1.List = dict.Keys
Else
    Me.ComboBox1.List = x
End If
Me.ComboBox1.DropDown


End Sub

我的职业列表（4610 个条目）在工作表职业的 C2 列中。

UserForm_Initialise 的代码如下：



Dim occupationName As Range

Dim ws As Worksheet
Set ws = Worksheets("Occupations")


  For Each occupationName In ws.Range(Cell1:="C2")
   With Me.ComboBox1
    .AddItem occupationName.Value
    .List(.ListCount - 1, 1) = occupationName.Offset(0, 1).Value
   End With
 Next occupationName

Me.ComboBox1.SetFocus
Me.ComboBox1.Value = "Type text to open a list of choices"

End Sub

当我运行代码时，我得到一个

运行时错误 '94' 无效使用 Null

在str = Me.ComboBox1.Value

我尝试了其他几个线程，也尝试使用 Nz 表示法，但 Excel VBA 无法识别它。

任何帮助将不胜感激。我可以进一步摆弄，仍然通过其他方式学习，但不幸的是，这个解决方案的时间不多了。

标签： excelvbacombobox

解决方案

I think you has not init the scripting dictionary correctly. the NEW statement is missing. So the scripting dictionary has never done its own hidden setup and will fail.

somewhere in the module head maybee

  dim dict as scripting.dictionary
  set dict = NEW scripting.dictionary 'has to be a new ones ;)


  sub dictionary_reset(dict as scripting.dictionary) 'reset the created dictionary
    on error resume next
    err.clear
    on error resume next
     dict.clearAll
     if err.number <>0 then  debug.print err.description 
  end sub

here you can run over your range and overwrite existing entrys or add new ones. if you want to start from beginning use the dictionary_reset sub first

  function addentry(dict as scripting.dictionary key as string ,entry as string ) as 
   boolean 'this you will need very often...
    addentry=false
    on error resume next
    err.clear
     if not dict.exist(key) then
        dict.item(key)=entry;
     else
        dict.add key , entry 
     end if
        if err.number <>0 then  debug.print err.description 
     addentry=err.number=0
  end function

this here will becomes true if a key is found. The result will be in entry. So you know what you can await before taking other actions. Sometimes handy.

  function searchentry(dict as scripting.dictionary key as string ,entry as string ) as 
        boolean
     searchentry=false
     err.clear 
     on error resume netx
     if dict.exists(key) then 
      searchentry=true
      entry=dict.item(key)
     endif
    if err.number <>0 then  debug.print err.description
  searchentry =err.number=0
  end function

it is understandable that you want to use as much as possible from known excel. with some practice you will go the opposite way. Think about "IF" in excel ;) Might be a idea to go immidiatly the opposite way. It makes also the code reusable in other applications. It is also a great idea to outsource such functions. Then you can debug them easy either by a test routine without the gui stuff. And most important they can be reused. I usually add also some error handling things.

BTW: VBA is also nothing else then VB6. If you do not find code in VBA you might find code for VB6.

Have fun here and enjoy VBA :)

推荐阅读