|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 dogingate 于 2019-1-13 20:13 编辑
就是根据一个库来实现自动提示,来源于本论坛某贴,俺只是把其中关键性的代码拿了出来并做了一定程度的修改,具体原贴忘记了,不好意思
其中“单元格录入自动提示”这个是我改的,请在“表格录入”这个表第2列,从第2行开始,任意单元格输入一个数字,如果"自动提示里"有这个数字的话,就会把“自动提示”里面的提示自动弹出来,比如你输入个4,或者3,或者7,单元格右边只会出现一条提示,如果输入2的话,会出来一堆。
1、首先在“表格录入”这个表里面创建一个listbox和一个textbox的控件,分别命名为Listobx1和textbox1(系统自动命令,无需更改),以下这段代码就是当你选中第2列某个单元格的时候激发的事件
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then GoTo 100: Exit Sub
If (Target.Column <> 2) Or (Target.Row < 2) Then GoTo 100: Exit Sub
col = Target.Column
Arr = Worksheets("自动提示").Range("a1").CurrentRegion
With Me.TextBox1
.Visible = True
.Text = ""
.Top = Target.Top
.Left = Target.Left
.Width = Target.Width
.Height = Target.Height + 5
.Activate
End With
With Me.ListBox1
.Visible = True
.Top = Target.Top
.Left = Target.Left + Target.Width
.Height = Target.Height * 10
End With
Exit Sub
100:
Me.TextBox1 = ""
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
End Sub
2.设置textbox1的事件,这里面最关键的就是Instr语句,用来判断你在单元格里的字符是否在自动提示库里存在
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim arrData As Variant
Dim arrString As Variant
If KeyCode = 13 Then
ActiveCell = TextBox1.Text
Me.TextBox1.Visible = False
Me.ListBox1.Visible = False
Exit Sub
End If
Dim oZd As Object: Set oZd = CreateObject("Scripting.Dictionary")
With Worksheets("自动提示")
arrData = .Range("a1").CurrentRegion
ReDim arrString(LBound(arrData, 1) To UBound(arrData, 1) - 1, 1 To 1)
If TextBox1.Text = "" Then
For i = 2 To UBound(arrData, 1)
oZd(arrData(i, 1)) = "fuck"
Next i
Else
For i = 2 To UBound(arrData, 1)
If InStr(arrData(i, 1), Me.TextBox1.Text) Then
oZd(arrData(i, 1)) = "fuck"
End If
Next i
End If
End With
If oZd.Count > 0 Then
Me.ListBox1.Clear
Me.ListBox1.List = oZd.keys
End If
End Sub
3.设置listbox1的事件,也就是你双击listbox1的其中一行,就自动填入你所激活的单元格中去
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell = ListBox1.Value
Me.ListBox1.Clear
Me.TextBox1 = ""
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
End Sub
|
评分
-
2
查看全部评分
-
|