excel用vba实现下拉菜单，不知道怎么加入一个根据条件显示下拉菜单功能

2021-03-10 11:58:11 1k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

weixin_53424922 墨衍会员 · AI 创作全网分发关注 + 加入社区

vba有下拉框的模糊查找_输入快10倍，带模糊查找的Excel下拉菜单来了！在Excel表格中设置下拉菜单很简单，但带模糊查询的你见过吗？兰色做了一个，先睹为快：估计会有同学说兰色是用VBA做的。No! 做这样的智能菜单只需要一个辅助公式即可。制作步骤：1、设置辅助公式在任一空白列设置辅助公式office365版本在B2输入公式(不需要向下复制公式)=FILTER(A2:A9,ISNUMBER(FIND(CELL("contents"),A2:A9)),"")注：CELL... 阅读详情

当判断列为否的时候，同一行的银行列点击不会出现下拉菜单，可以随意输入内容

Private Sub ListBox1_Click()
ActiveCell.Value = Me.ListBox1.Value
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
End Sub

Private Sub TextBox1_Change()
Dim arr, i%, j%, d
Set d = CreateObject("scripting.dictionary")
arr = Sheet2.Range("A1").CurrentRegion
For i = 2 To UBound(arr)
If InStr(arr(i, 1), Me.TextBox1.Value) Then
d(arr(i, 1)) = ""

End If
Next
Me.ListBox1.Clear
If d.Count >= 1 Then Me.ListBox1.List = d.keys

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Me.TextBox1.Visible = False: Me.ListBox1.Visible = False: Exit Sub
If Target.Column <> 3 Then Me.TextBox1.Visible = False: Me.ListBox1.Visible = False: Exit Sub
If Target.Row < 7 Then Me.TextBox1.Visible = False: Me.ListBox1.Visible = False: Exit Sub
Dim arr, i%, j%, d
Set d = CreateObject("scripting.dictionary")
arr = Sheet2.Range("A1").CurrentRegion
For i = 2 To UBound(arr)
d(arr(i, 1)) = ""
Next
With Me.TextBox1
.Top = Target.Top
.Left = Target.Left
.Width = Target.Width
.Height = Target.Height
.Activate
.Value = ""
.Visible = True
End With
With Me.ListBox1
.Clear
.Top = Target.Offset(0, 1).Top
.Left = Target.Offset(0, 1).Left
.Height = Target.Offset(0, 1).Height * 4
.Width = Target.Offset(0, 1).Width

.List = d.keys
.Visible = True
End With
End Sub

...全文