|
- Option Explicit
- Dim i&
- Dim arr, d As Object, S$
- Private Sub UserForm_Initialize()
- Dim key$
- arr = Sheet2.Range("A1").CurrentRegion
- Set d = CreateObject("scripting.dictionary")
- For i = 2 To UBound(arr)
- If Trim(arr(i, 1)) <> "" Then
- key = Trim(arr(i, 1))
- d(key) = i
- Else
- d.Item(key) = d.Item(key) & "|" & i
- End If
- Next
- Sheet1.Select
- ListBox1.List = d.keys
- End Sub
- Private Sub cmd取消_Click()
- Unload Me
- End Sub
- Private Sub cmd确认_Click()
- Dim s2$
- For i = 0 To ListBox2.ListCount - 1
- If ListBox2.Selected(i) Then
- s2 = IIf(s2 = "", ListBox2.List(i, 0), s2 & "," & ListBox2.List(i, 0))
- End If
- Next
- ActiveCell = S
- ActiveCell.Offset(0, 1) = s2
- Unload Me
- End Sub
- Private Sub ListBox1_Change()
- Dim key$, j%, t, b(), n%
- S = ""
- For i = 0 To ListBox1.ListCount - 1
- If ListBox1.Selected(i) Then
- key = ListBox1.List(i, 0)
- Rem 被选中的二级目录
- S = IIf(S = "", key, S & "," & key)
- Rem 被选中的三级目录
- t = Split(d.Item(key), "|")
- For j = 0 To UBound(t)
- n = n + 1: ReDim Preserve b(1 To n)
- b(n) = arr(t(j), 2)
- Next
- End If
- Next
- ListBox2.List = b
- End Sub
复制代码 |
|