|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Option Explicit
Private Sub listbox1_dblclick(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell.Value = ListBox1.Value
Me.ListBox1.Clear
Me.TextBox1 = ""
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
End Sub
Private Sub Textbox1_Keyup(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim i As Integer
Dim Language As Boolean
Dim myStr As String
Me.ListBox1.Clear
With Me.TextBox1
For i = 1 To Len(.Value)
If Asc(Mid$(.Value, i, 1)) > 255 Or Asc(Mid$(.Value, i, 1)) < 0 Then
Language = True
myStr = myStr & Mid$(.Value, i, 1)
Else
myStr = myStr & LCase(Mid$(.Value, i, 1))
End If
Next
End With
With Sheet2
For i = 2 To .Range("A65536").End(xlUp).Row
If Language = True Then
If Left(.Cells(i, 1).Value.Len(myStr)) = myStr Then
Me.ListBox1.AddItem.Cells(i, 1).Value
End If
Else
If Left(.Cells(i, 2).Value, Len(myStr)) = myStr Then
Me.ListBox1.AddItem.Cells(i, 1).Value
End If
End If
Next
End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
If Target.Count = 1 Then
If Target.Column = 1 And Target.Row > 1 Then
With Me.TextBox1
.Visible = True
.Top = Target.Top
.Left = Target.Left
.Width = Target.Width
.Height = Target.Height
End With
With Me.ListBox1
.Clear
.Visible = True
.Top = Target.Top
.Left = Target.Left + Target.Width
.Width = Target.Width
.Height = Target.Height * 5
For i = 2 To Sheet2.Range("A65536").End(xlUp).Row
.AddItem Sheet2.Cells(i, 1).Value
Next
End With
Else
Me.ListBox1.Clear
Me.TextBox1 = ""
Me.ListBox1.Visible = False
Me.TextBox1.Visible = False
End If
End If
End Sub |
|