marshallgrad
01-15-2009, 12:48 PM
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
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