|
- Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
- With ListBox1
- For i = 0 To .ListCount
- If .Selected(i) Then
- arr = Array(.Text, .Column(1), .Column(2))
- Exit For
- End If
- Next
- End With
- ActiveCell.Resize(1, 3) = arr
- TextBox1.Text = ""
- ListBox1.Visible = False
- TextBox1.Visible = False
- End Sub
- Private Sub TextBox1_Change()
- s = TextBox1.Text
- arr = [a1].CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To 3)
- For i = 1 To UBound(arr)
- If InStr(1, arr(i, 1), s) > 0 Then
- k = k + 1
- brr(k, 1) = arr(i, 1)
- brr(k, 2) = arr(i, 2)
- brr(k, 3) = arr(i, 3)
- End If
- Next
- ListBox1.List = brr
- End Sub
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If Target.Column = 6 Then
- With TextBox1
- .Visible = True
- .Top = Target.Top
- .Left = Target.Left
- .Height = Target.Height
- .Width = Target.Width
- .Activate
- End With
- With ListBox1
- .Visible = True
- .Top = Target.Top
- .Left = Target.Offset(0, 1).Left
- .Height = Target.Height * 10
- .Width = 200
- .ColumnCount = 3
- End With
- Else
- ListBox1.Visible = False
- TextBox1.Visible = False
- End If
- End Sub
复制代码 |
|