Has it been touched by Rube Goldberg?

[gmaxey]

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

[fumei]

It seems fairly efficient to me, at least on glancing through it. Off the top of my head, I do not see anything jumping out that would make it better.

[fumei]

For i = ActiveDocument.Variables.Count To 1 Step -1


I assume there are NO other Variables to mess with the list!

[macropod]

Perhaps a bit more efficient (or at least more compact):
With ColX
For i = 1 to 26
.Add Asc(64+i), Asc(64+i)
Next i
End With

[fumei]

That Paul.

Gotta love that guy.

[gmaxey]

Gerry, Fumie,

Thanks. I thought maybe using a class might be better but those things still give me the shivers to I avoided that route. A-Z was just an example and of course Paul makes it easier ;-)

[gmaxey]

Paul,

Do you mean:

For i = 1 To 26
.Add Chr(64 + i), Chr(64 + i)
Next i



Perhaps a bit more efficient (or at least more compact):
With ColX
For i = 1 to 26
.Add Asc(64+i), Asc(64+i)
Next i
End With

[macropod]

Hi Greg,

What can I say :doh:

[gmaxey]

Said the same thing many times. Still it was a point in the right direction.
Thanks

[Tinbendr]

You can use a dash to make a range.

If ActiveDocument.Variables(1) Like "[A-Z]" Then

Listbox is already a collection. I don’t understand why you’re using a New Collection. The main advantage of using a new collection is to create a unique list, unlike listbox that will take duplicates.

But let’s assume for this exercise, that you want to use New Collection.

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

The use of arrX is unnecessary.

If colX.Count > 0 Then
.ListBox1.Clear
For i = 1 To colX.Count
ListBox1.AddItem colX(i)
Next i
End If

Produces the same result.

And
>> .ListIndex = 0
>> .ListIndex = -1

You only needed one, right?

David

[gmaxey]

Tinbender,

Your points are well taken. Especially the one about not really needing the array. The example was scratched together rather quickly so yes .ListIndex = -1 was the one intended.

The actual project (worthwhile or not ;-) is to create listbox which contains the pre-defined name of each of the predefined custom document properties that has no assigned value. I have done that using code similiar to that in the example.

Thanks.