6,216
社区成员
发帖
与我相关
我的任务
分享Excel的combo box下拉选项如何实现关键字筛选
我做了user form来录入订单内容, 当中有combo box控件, 下拉显示产品列表。 因为产品很多,我希望录入订单时能根据产品全称当中的个别字眼,即关键字,通过在combo box 敲入一个关键字，点击combo box的下拉按钮，就会出现仅包含关键字的候选产品名称，从而使下拉列表大大缩短，便于查找。
combo box自身是可以根据手动输入某名称的首字符来搜索近似的选项名从而自动完成候选项的输入，并且这时按下下拉按钮就能得到以这个字符开头的选项。现在主要就是想使用关键字也能得到同样的效果。
要怎样实现呢？请帮忙哦，谢谢了！
我在http://www.ozgrid.com/forum/showthread.php?t=59306看到有人提问类似的问题，大家可以参考那里的解答
Private Sub CommandButton1_Click()
Dim lCount As Long
Dim rFoundCell As Range
Dim lLoop As Long
Dim wSheet As Worksheet
Dim StrFind As String
Set wSheet = Worksheets("Food_List")
Set rFoundCell = wSheet.Range("A1")
StrFind = TB5.Value
lLoop = WorksheetFunction.CountIf(wSheet.Columns(1), "*" & StrFind & "*")
If lLoop > 0 Then ComboBox1.RowSource = vbNullString
For lCount = 1 To lLoop
Set rFoundCell = wSheet.Columns(1).Find(What:=StrFind, After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
ComboBox1.AddItem rFoundCell
Next lCount
End Sub
combo box自身是可以根据手动输入某名称的首字符来搜索近似的选项名从而自动完成候选项的输入，并且这时按下下拉按钮就能得到以这个字符开头的选项。现在主要就是想使用关键字也能得到同样的效果。
要怎样实现呢？请帮忙哦，谢谢了！
我在http://www.ozgrid.com/forum/showthread.php?t=59306看到有人提问类似的问题，大家可以参考那里的解答
Private Sub CommandButton1_Click()
Dim lCount As Long
Dim rFoundCell As Range
Dim lLoop As Long
Dim wSheet As Worksheet
Dim StrFind As String
Set wSheet = Worksheets("Food_List")
Set rFoundCell = wSheet.Range("A1")
StrFind = TB5.Value
lLoop = WorksheetFunction.CountIf(wSheet.Columns(1), "*" & StrFind & "*")
If lLoop > 0 Then ComboBox1.RowSource = vbNullString
For lCount = 1 To lLoop
Set rFoundCell = wSheet.Columns(1).Find(What:=StrFind, After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
ComboBox1.AddItem rFoundCell
Next lCount
End Sub
...全文
请发表友善的回复…
发表回复
michmichcat 2008-01-06
- 打赏
- 举报
我找过英文的论坛，有人问过跟我一样的问题，但是很可惜也没有答案，有人提供了使用listbox控件的解答方案，但是我希望得到用combobox来解决的办法。。。下面是那个英文的帖子的链接，麻烦高手有空看看。。
http://www.ozgrid.com/forum/showthread.php?t=65707
“I have a combo box with match entry = 1,
Is it possible to select an item from the list when I start typing parts of the item.
I have a large list and don’t always know the beginning characters.
Example, I type foq and in the combo box it goes to Ets.Foquet
I have already tried typing a * and so on. Before, *foq, #foq。。。”
http://www.ozgrid.com/forum/showthread.php?t=65707
“I have a combo box with match entry = 1,
Is it possible to select an item from the list when I start typing parts of the item.
I have a large list and don’t always know the beginning characters.
Example, I type foq and in the combo box it goes to Ets.Foquet
I have already tried typing a * and so on. Before, *foq, #foq。。。”
changjiangzhibin 2008-01-04
- 打赏
- 举报
up
