tweaking combobox to collate based on date

[marshallgrad]

Hey gang,
I need a little help on a project to tweak a macro I am using currently. We use a Terminal emulator that has Visual Basic for Applications built into it. I was able, with the help of this forum to develop a Macro that populates a combobox with a list of names and telephone numbers to go along with that. The list is derived from a TEXT file that I have stored. I can add to the list of names and as such, my combobox list increases in size. I would like to be able to collate the list of names in the combobox so that the name added last is actually the name displayed first, based on the entry date of the name stored.
When adding names to the TEXT file, I use the following. (it is part of a separate userform)

Private Sub ADD_NAME_OK_Click()
Dim TITLE, FIRSTNAME, UFIRSTNAME, NICKNAME, UNICKNAME, LASTNAME, ULASTNAME As String
Dim BEEPERTELEPHONE, COMMENT, UCOMMENT As String
Dim fso, f
' IF YOU GET A "SUBSCRIPT OUR OF RANGE ERROR" THEN YOU HAVE A PROBLEM MOST LIKELY
' WITH THE TEXT FILE THAT WAS SAVED TO THE H: DRIVE.
' IT REQUIRES THAT A [ENTER] BE USED AFTER THE LAST LINE ITEM
' DATA FORMAT = |FIRTSNAME|LASTNAME|PHONE|TITLE|NICKNAME|COMMENT|DATE|
' EXAMPLE:   |VIRGIL|JONES|6325|RN|BUTCH|COMMENT|7/21/2008|    
Const ForReading = 1, ForWriting = 2, ForAppending = 8
'COMBOBOX1 = TITLE <populated with predefined choices>
'TEXTBOX1 = BEEPER/TELEPHONE NUMBER
'TEXTBOX2 = FIRST NAME
'TEXTBOX3 = LAST NAME
'TEXTBOX4 = NICKNAME
'TEXTBOX5 = COMMENT
TITLE = NEWPERSONENTRY.ComboBox1.text
BEEPERTELEPHONE = NEWPERSONENTRY.TextBox1.text
    If BEEPERTELEPHONE = "" Then BEEPERTELEPHONE = "**"
FIRSTNAME = NEWPERSONENTRY.TextBox2.text
LASTNAME = NEWPERSONENTRY.TextBox3.text
NICKNAME = NEWPERSONENTRY.TextBox4.text
    If NICKNAME = "" Then NICKNAME = "##"
COMMENT = NEWPERSONENTRY.TextBox5.text
    If COMMENT = "" Then COMMENT = "@@"
'CONVERT TYPED ENTRIES INTO ALL UPPERCASE LETTERS.
UFIRSTNAME = UCase(FIRSTNAME)
ULASTNAME = UCase(LASTNAME)
UNICKNAME = UCase(NICKNAME)
UCOMMENT = UCase(COMMENT)
'==========================================================
'THIS LITTLE SUB WILL CREATE A TEXT FILE AND WRITE THE DATA.
'IN THIS INSTANCE IT WILL FEED A CARRIAGE RETURN AND LINE FEED FIRST
'THIS MAKES THE DATA FALL ON A SEPARATE LINE.
'object.OpenTextFile(filename[, iomode[, create[, format]]])
'WHERE IOMODE CAN EQUAL ONE OF THE BELOW CONSTANTS.
'THE THIRD PART "TRUE" CAN BE EITHER  0,-1,-2
'0 = OPEN FILE AS ASCII
'-1 = TRUE = OPEN FILE AS UNICODE
'-2 = FALSE = OPEN FILE AS DEFAULT TYPE
    ENTRYDATE = FormatDateTime(Now, 2)
    'WILL RETURN THE DATE IN FORMAT 7/3/2008
 
   Set fso = CreateObject("Scripting.FileSystemObject")
 
   'this works for setting on the H:\ drive.
   Set f = fso.OpenTextFile("H:\CRITICALNAMES.txt", 8, True)
 
        f.WRITE "|" & UFIRSTNAME & "|" & ULASTNAME & "|" & BEEPERTELEPHONE & "|" & TITLE & "|" & UNICKNAME & "|" & UCOMMENT & "|" & ENTRYDATE & "|" & vbCrLf
 
'FLUSH THE BUFFER AND CLOSE THE FILE
   f.Close
    If TITLE = "DOCTOR" Then
            NEWPERSONENTRY.Hide
            Unload NEWPERSONENTRY
            CRITICALDOCTOR.Show
        Else
            NEWPERSONENTRY.Hide
            Unload NEWPERSONENTRY
            CRITICALOTHER.Show
    End If
 
End Sub

If we were to open the TEXT file that went with the above, it might look something like this:

|LONE|RANGER|330-6079|DOCTOR|##|@@|7/27/2008|
|DEPUTY|DOG|VA-500|PHARM-D|##|PHARMACY-5179|7/28/2008
|JONATHAN|DOE|EXT-4520| NURSE PRACTITIONER|JOHN|BLUE CLINIC|7/30/2008|

What I want to do is be able to add the above entries to the below combobox based on sorting them by the date (last piece of the text line entry) so that the lastest date is the first entry in the list. In the above listed example, the names would display as

1. LONE RANGER
2. DEPUTY DOG
3. JONATHAN DOE

But what I want to do is have them display as

1. JONATHAN DOE <date = 7/30/2008>
2. DEPUTY DOG <date = 7/28/2008>
3. LONE RANGER <date = 7/27/2008>

Here is my current INITIALIZE STATEMENT for loading the combobox form

Private Sub UserForm_Initialize()
'INITIALIZE THE LOADING OF THE NAMES INTO THE COMBOBOX.
Dim LISTNAME As String
Dim LISTNAMED() As String
Dim COMPLETENAME As String
    With CRITICALDOCTOR
 
        Open "H:\CRITICALNAMES.txt" For Input As #1
 
        Do Until EOF(1)   ?############################################
 
            Line Input #1, LISTNAME
 
            '=========================================================
                'TEXT FILE ENTRY SHOULD LOOK SOMETHING LIKE THIS.
                '|FIRST|LAST|PHONE|TITLE|NICKNAME|COMMENT|DATE|
                '|VIRGIL|JONES|4515|OTHER|BUTCH|LAB PERSON|7/21/2008|
                '|VIRGIL|JONES|**|OTHER|##|@@|7/21/2008|
 
                LISTNAMED = Split(LISTNAME, "|", 7)
                'LISTNAMED(0) = ""
                'LISTNAMED(1) = FIRST NAME
                'LISTNAMED(2) = LASTNAME
                'LISTNAMED(3) = PHONE NUMBER/BEEPER NUMBER OR "**"
                'LISTNAMED(4) = TITLE
                'LISTNAMED(5) = NICKNAME OR "##"
                'LISTNAMED(6) = COMMENT OR "@@"
                'LISTNAMED(7) = DATE ENTERED INTO LIST
            '===========================================================
 
 
           If LISTNAMED(4) = "DOCTOR" Then
                    COMPLETENAME = LISTNAMED(2) & ", " & LISTNAMED(1) & SPACE(5) & "[ " & LISTNAMED(4) & " = " & LISTNAMED(3) & " ]"
                    CRITICALDOCTOR.ComboBox1.AddItem COMPLETENAME
                Else
                    'DO NOTHING
                    'NO NAMES ADDED TO LIST
            End If
 
        Loop    '#################################################
Close #1
    End With
End Sub

I appreciate any help or suggestions that can be given. please understand that I am somewhat of a novice at code writing and would appreciate any detail that you can give along with code examples.

Thanks in advance,
Butch

[rocheey]

I'd to it without code - or at least, with little code. Rather than looking at using a sorting algorithm, and sorting by multiple columns, etc, every time you open the file, which will take increasing amounts of time as your list grows, I would do the following:

First, open your existing text file in Excel. Since it is already formatted with "delimiters" (the "|" that tells VBA where "UFIRSTNAME" ends and "ULASTNAME" starts), Excel will show the text file in individual cells. Use Excels Sorting function to sort your existing text, using the columns you want, and sort it OLDEST to NEWEST, so your newest entries, the ones you want at the TOP of your combobox, are at the END.

You will only have to do this once. It may already be sorted like this. Now, every time you add a new entry, it will be placed at the end of the file, basically in the exact, reverse order that you want.

Now to reverse the order, when you place them into the combo box, use the optional "varindex" parameter of the "Additem" method. The "varindex" parameter lets you select WHERE in the list you want to add an item. Without this second parameter, it always gets placed at the end. You can use this parameter to always place the item at the BEGINNING (index 0), so you place your "reversed" text file into the combobox BACKWARDS, which effectively reverses the order.

So, instead of using :
CRITICALDOCTOR.ComboBox1.AddItem COMPLETENAME

use:
CRITICALDOCTOR.ComboBox1.AddItem COMPLETENAME,0



thats it - a one time sort of the text file, and two keystrokes of VBA code.