Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.ListBox1
If Target.Row > 1 And Target.Row < 6 And Target.Column = 3 Then
.Left = Target.Offset(0, 1).Left
.Top = Target.Offset(0, 1).Top
.MultiSelect = fmMultiSelectExtended
.Visible = True
Else
Call test4
.Visible = False
End If
End With
End Sub
Private Sub ListBox1_Change()
Call test3
End Sub
'求不重复项
Sub test1()
Dim A, d, k, i
A = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(A)
d(A(i, 1)) = ""
Next i
k = d.keys
'加载条目
With Sheet1.ListBox1
For i = 0 To UBound(k)
.AddItem k(i)
Next i
End With
End Sub
'删除条目
Sub test2()
Dim i
With Sheet1.ListBox1
For i = .ListCount To 1 Step -1
.RemoveItem .ListCount - 1
Next i
End With
End Sub
'读取选取
Sub test3()
Dim i%, str$
On Error Resume Next
With Me.ListBox1
For i = 0 To .ListCount
If .Selected(i) Then str = str & "," & .List(i)
Next i
ActiveCell = Mid(str, 2)
End With
End Sub
'取消所有选取
Sub test4()
Dim i%, str$
On Error Resume Next
With Me.ListBox1
For i = 0 To .ListCount
If .Selected(i) Then .Selected(i) = False
Next i
ActiveCell = Mid(str, 2)
End With
End Sub
因为ListBox(.ListCount )不存在,所以报错!!
改为:
'读取选取
Sub test3()
Dim i%, str$
'On Error Resume Next
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then str = str & "," & .List(i)
Next i
ActiveCell = Mid(str, 2)
End With
End Sub
因为ListBox(.ListCount )不存在,所以报错!!
改为:
'读取选取
Sub test3()
Dim i%, str$
'On Error Resume Next
With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then str = str & "," & .List(i)
Next i
ActiveCell = Mid(str, 2)
End With
End Sub