I've been struggling with this one for some time trying everything I can
think of to no avail. . . Problems only with the last 12 lines or so
following the 2nd Next i. Am trying to get code to delete the strComment if
it contains only one item, if 1 then it is OK and sets it to the value in
the File Properties Comments box. At the 2nd Next i, the String gives a list
of numbers, like 123, 12345, 12346, 12347 etc. that would go in the box.
If the string contained only the "123" then it would be blank. Should be a
simple fix. Other errors i get when switching things around (mystr,
StrComment) are invalid qualifiers or object not defined. . . .


Public Sub FillFilePropsComments() '(Optional control As IRibbonControl)

Dim ws As Worksheet
Dim Strlist() As String ' list for permit numbers
Dim ptr As Integer ' pointer/ counter for strList
Dim PermitNumber As Variant
Dim DupFound As Boolean
Dim StrComment As String
Dim PropAuthor As String
Dim mystr As DocumentProperty

ptr = 0 ' init pointer - currently there are zero permit numbers in list
ReDim Strlist(1 To Sheets.Count) ' init size of list, should not be more
Permits than sheets

For Each ws In Worksheets
PermitNumber = GetPermitNumber(ws) ' get the permit number
DupFound = False ' assume it is not already in list
For i = ptr To 1 Step -1 ' start from back of list, see if it
is a dup
If PermitNumber = Strlist(i) Then
DupFound = True
Exit For
End If
Next i
If Not DupFound Then ' it is not already in list
ptr = ptr + 1 ' make a place for it in the list by pointing to
next empty slot
Strlist(ptr) = PermitNumber ' store new permit number
End If
Next ws

ReDim Preserve Strlist(1 To ptr) ' resize list to number of permits
QuickSort Strlist, LBound(Strlist), UBound(Strlist) ' sort the list

For i = 1 To ptr ' get the list into one long string
StrComment = StrComment & Strlist(i) & ", "
Next i

mystr = StrComment 'err 91 occurs here

mystr = Replace(mystr, ",", " ")
mystr = Application.Trim(mystr)
mystr = Replace(mystr, " ", ", ")

Dim newStr() As New Collection

' reDim mystr As New Collection ? 'not sure if this is right approach
so '

If mystr = (mystr.Count = 1) Then
StrComment = ""
ElseIf mystr (1) Then
StrComment = mystr

End If

' store the string in the FileComments Property box
ActiveWorkbook.BuiltinDocumentProperties("Comments ").Value = StrComment

End Sub