gmaxey
12-30-2010, 04:27 PM
I have a project that requires me to populate a listbox (Listbox1) with a predefined list of items. Once one of those items is used then Listbox1 needs to be repopulated minus the items used and the item used needs to be added to another collection (ListBox2). The name of the items used are stored in a collection so if the form is dismissed and then recalled both Listbox1 and Listbox2 will populate correctly.
I have cobbled together some code that does the job. You can demo it by creating a simple form with two listboxes. Basically I
1. add all the predefined terms to a new collection
2. remove any item from the new collection that is already part of the stored document collection
3. Add items removed to the Listbox2
4. Build an array with the items left in the collection
5. Populate Listbox1 with the array.
Have I gone from a to b through z here or is there a more efficient/compact way to do this. Thanks?
Private Sub UserForm_Initialize()
LoadListboxes
End Sub
Private Sub ListBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNull(ListBox1.Value) Then
ActiveDocument.Variables(ListBox1.Value).Value = ListBox1.Value
LoadListboxes
End If
End Sub
Sub LoadListboxes()
Dim colX As Collection
Dim arrX() As String
Dim i As Long
Set colX = New Collection
With colX
.Add "A", "A"
.Add "B", "B"
.Add "C", "C"
.Add "D", "D"
.Add "E", "E"
.Add "F", "F"
.Add "G", "G"
.Add "H", "H"
.Add "I", "I"
.Add "J", "J"
.Add "K", "K"
.Add "L", "L"
.Add "M", "M"
.Add "N", "N"
.Add "O", "O"
.Add "P", "P"
.Add "Q", "Q"
.Add "R", "R"
.Add "S", "S"
.Add "T", "T"
.Add "U", "U"
.Add "V", "V"
.Add "W", "W"
.Add "X", "X"
.Add "Y", "Y"
.Add "Z", "Z"
End With
On Error Resume Next
With Me
.ListBox2.Clear
For i = ActiveDocument.Variables.Count To 1 Step -1
If ActiveDocument.Variables(1) Like "[ABCDEFGHIJKLMNOPQRSTUVWXYZ]" Then
.ListBox2.AddItem ActiveDocument.Variables(i).Name, 0
colX.Remove (ActiveDocument.Variables(i).Name)
End If
Next i
On Error GoTo 0
If colX.Count > 0 Then
ReDim arrX(colX.Count - 1) As String
For i = 1 To colX.Count
arrX(i - 1) = colX(i)
Next i
.ListBox1.Clear
.ListBox1.List = arrX()
End If
With .ListBox2
If .ListCount > 0 Then
.ListIndex = 0
.ListIndex = -1
End If
End With
End With
End Sub
I have cobbled together some code that does the job. You can demo it by creating a simple form with two listboxes. Basically I
1. add all the predefined terms to a new collection
2. remove any item from the new collection that is already part of the stored document collection
3. Add items removed to the Listbox2
4. Build an array with the items left in the collection
5. Populate Listbox1 with the array.
Have I gone from a to b through z here or is there a more efficient/compact way to do this. Thanks?
Private Sub UserForm_Initialize()
LoadListboxes
End Sub
Private Sub ListBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNull(ListBox1.Value) Then
ActiveDocument.Variables(ListBox1.Value).Value = ListBox1.Value
LoadListboxes
End If
End Sub
Sub LoadListboxes()
Dim colX As Collection
Dim arrX() As String
Dim i As Long
Set colX = New Collection
With colX
.Add "A", "A"
.Add "B", "B"
.Add "C", "C"
.Add "D", "D"
.Add "E", "E"
.Add "F", "F"
.Add "G", "G"
.Add "H", "H"
.Add "I", "I"
.Add "J", "J"
.Add "K", "K"
.Add "L", "L"
.Add "M", "M"
.Add "N", "N"
.Add "O", "O"
.Add "P", "P"
.Add "Q", "Q"
.Add "R", "R"
.Add "S", "S"
.Add "T", "T"
.Add "U", "U"
.Add "V", "V"
.Add "W", "W"
.Add "X", "X"
.Add "Y", "Y"
.Add "Z", "Z"
End With
On Error Resume Next
With Me
.ListBox2.Clear
For i = ActiveDocument.Variables.Count To 1 Step -1
If ActiveDocument.Variables(1) Like "[ABCDEFGHIJKLMNOPQRSTUVWXYZ]" Then
.ListBox2.AddItem ActiveDocument.Variables(i).Name, 0
colX.Remove (ActiveDocument.Variables(i).Name)
End If
Next i
On Error GoTo 0
If colX.Count > 0 Then
ReDim arrX(colX.Count - 1) As String
For i = 1 To colX.Count
arrX(i - 1) = colX(i)
Next i
.ListBox1.Clear
.ListBox1.List = arrX()
End If
With .ListBox2
If .ListCount > 0 Then
.ListIndex = 0
.ListIndex = -1
End If
End With
End With
End Sub