|
楼主 |
发表于 2016-11-16 22:32
|
显示全部楼层
'刷新控件
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Column = 6 Then '可能会改
With TextBox1
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width
.Height = Target.Height
.Activate
.Value = ""
.Visible = True
End With
With ListBox1
.Left = Target.Offset(0, 1).Left
.Top = Target.Top
.Height = Target.Height * 10
.Width = 300
.ColumnCount = 3 '可能会改
.Visible = True
End With
Else
TextBox1.Visible = False
ListBox1.Visible = False
End If
End If
End Sub
'写入Listbox
Private Sub TextBox1_Change()
Dim txt, arr, brr, i, j, s
txt = TextBox1.Text
arr = [a1].CurrentRegion
ReDim brr(1 To UBound(arr) + 1, 1 To UBound(arr, 2)) '多创建一行,为避免可能无法显示最后一行
For i = 1 To UBound(arr)
If arr(i, 1) Like txt & "*" Then
s = s + 1
For j = 1 To UBound(arr, 2)
brr(s, j) = arr(i, j)
Next j
End If
Next
ListBox1.List = brr
End Sub
'写入单元格
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim i, arr
With ListBox1
arr = .List
For i = 0 To .ListCount
If .Selected(i) Then Exit For
Next
ActiveCell.Resize(1, UBound(arr, 2) + 1) = Application.Index(arr, i + 1, 0)
.Visible = False
End With
TextBox1.Visible = False
End Sub
学习4.rar
(26.4 KB, 下载次数: 10)
|
|