|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 jiaruishuang 于 2013-6-23 16:01 编辑
实现效果,如果combobox1中的下拉菜单条目过多,在输入关键字时,会自动筛选包含该关键字的下拉菜单。
商品名称列表在sheet2中的A列,工作表名为“基本设置”
首先,打开窗体后,加载所有条目,
Private Sub UserForm_Initialize()
Dim i As Long, x As Long
ComboBox1.Clear
With Sheet2
i = .Range("a65536").End(xlUp).Row
For x = 2 To i
ComboBox1.AddItem .Range("a" & x).Value
Next
End With
End Sub
方法一、利用字典方法和filter函数
Private Sub ComboBox1_Change()
Dim dic As New Dictionary
Dim i As Long, x As Long
On Error Resume Next'为避免出现重复,加上此句.
With Sheet2
i = .Range("a65536").End(xlUp).Row
For x = 2 To i
dic.Add .Range("a" & x).Value, ""
Next
End With
If VBA.Len(ComboBox1.Value) <> 0 Then
ComboBox1.List = Filter(dic.Keys, ComboBox1.Value)
ComboBox1.DropDown
End If
If VBA.Len(ComboBox1.Value) = 0 Then
ComboBox1.List = dic.Keys
End If
End Sub
方法二、利用ADO方法和LIKE关键字
Private Sub ComboBox1_Change()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String, i As Long, x As Long
On Error Resume Next
cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
If VBA.Len(ComboBox1.Value) <> 0 Then
sql = "select distinct 商品名称 from [基本设置$] where 商品名称 like '%" & ComboBox1.Value & "%'"
rst.Open sql, cnn, adOpenKeyset, adLockOptimistic
ComboBox1.Column = rst.GetRows
ComboBox1.DropDown
End If
if vba.len(combobox1.value)=0 then
sql="select distinct 商品名称 from [基本设置$]"
rst.open sql,cnn,adopenkeyset,adlockoptimistic
combobox1.column=rst.getrows
end if
cnn.Close
Set cnn = Nothing
set rst = nothing
End Sub
方法一更为简单快捷。
|
评分
-
1
查看全部评分
-
|