Looping Through Custom List Values

[Jim Aksel]

I am attempting to write code that will examine the items in a
custom value list. For example, a custom list for Text2.
For each item in the list, I need to go do something depending on the value.
For now, I will just put up a message box.
The following code seems to be a start, but it will not run.


Public Sub generate()
On Error Resume Next
Dim counter As Integer
Dim result As VbMsgBoxResult
counter =
Application.ActiveProject.CustomFieldValueList(pjCustomTaskText2).Count
'Apparently No Count property?
Debug.Print (counter)
Dim j As Integer
Dim myString As String
For j = 1 To 4 'counter 'No work.
'idea is to use "counter" instead of 4
Debug.Print ("j=" & j)
myString =
Application.ActiveProject.CustomFieldValueListGetItem(pjCustomTaskText2,
pjValueListValue, j)
result = MsgBox(Str(j) & " " & myString, vbInformation)
Next
End Sub

Any ideas would be helpful.

 

[Jack Dahlgren]

I haven't played around with enumerating the items in the list, but for
cases where there is no count property, I've just used brute force and
worked through the list by incrementing the index until I get an error.

For example you might try using CustomFieldValueGetItem and increase Index
until the result is no longer valid. That should give you a count.

Your for j = 0 to counter code should work given a proper integer value for
counter. Since you have not set it earlier in the code (because count does
not return a value apparently) it won't work later. But if you add a line
counter = 4
then run the code it should be working.

Ah... I see you are using CustomFieldValueGetItem a bit later. Why not just
use that part of the code with an on error statement to bail out when it
reaches the end? In otherwords, don't bother to count before hand. Just
display until there is no more.

-Jack

 

[Jim Aksel]

With some help from Jack, the following code works properly for Text2 of the
Task table. It is pretty easy to change this to any of the custom variables,
you can get a list of all potential items in the Object Browser. Please see
my comments after the code for additional information:

Public Sub LoopCustomValueList()
On Error GoTo ErrorHandler
Dim counter As Long
Dim result As VbMsgBoxResult

Dim myString As String

'Verify there are items on the list
counter = 1
Do
Debug.Print ("counter=" & counter)
myString = CustomFieldValueListGetItem(pjCustomTaskText2,
pjValueListValue, counter)
'Go do something with list values...
result = MsgBox(Str(counter) & " " & myString, vbInformation)
counter = counter + 1
Loop 'Number of list items could be very large, but doubtful.
ProcessingContinues:
Debug.Print ("Success! Counter: " & counter)
'Go do something important
Exit Sub

ErrorHandler:
If Err.Number = 1004 Then
counter = counter - 1
GoTo ProcessingContinues
Else
MsgBox ("Unknown error while counting Text2 List Items" & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
Err.Description)
Exit Sub
End If
End Sub

****************************
The idea would be to copy the list value items into some Collection Object
and then move forward from there; much more important than a message box.

Potential problems - what if the user has not specified a ValueList, that
is, the attribute is set to None or Formula? That is OK if there are no
items on the value list. But what if the user made a value list and then
abandoned it? What if Text2 is not unique -- that is the user has identical
values on his Value List that only differ by description (or not at all).
These issues need to be handled programactically, but not necessarily within
this subroutine.

This subroutine will also identify the count of items on the value list.
--
If this post was helpful, please consider rating it.

Jim
It's software; it's not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project