Have a Combo Box list populate external data

[jlawson]

Does anyone have some VBA code that will allow the combo box in Word to
access an Excel spread sheet or .txt file for it's dropdown list and
populate that data?

I can create it to list the array I put into the code but I want it to
access a spread sheet other people can update easily without messing
with the code. I apologize if this has been posted before. Thank you
for your time.

 

[jlawson]

Does anyone have some VBA code that will allow the combo box in Word to
access an Excel spread sheet or .txt file for it's dropdown list and
populate that data?

I can create it to list the array I put into the code but I want it to
access a spread sheet other people can update easily without messing
with the code. I apologize if this has been posted before. Thank you
for your time.

I forgot to add that I am using MS Word XP

 

[jlawson]

Ok, I have the combo box on the word document itself. I created the
txt file. When I click on the drop down box nothing appears in the
drop down and if I type in it I have to debug because VBA says that the
object can not be found. The debug menu highlights the
"cboItems.AddItem TextLine" line of the code and I can hover my mouse
over the "TextLine" part to see the first name in my txt file, in this
case "Mary" is the first name. I'm sorry I don't have much experience
with combo boxes. Below is the exact code.


Private Sub ComboBox3_Change()
ComboBox3.ColumnCount = 1
Dim FileHandle As Integer
Dim FName As String
Dim TextLine As String
Const FilePath As String = "c:\testfile.txt"


FName = Dir(FilePath)

'ComboBox1.ColumnCount = 1


If FName <> "" Then
FileHandle = FreeFile


Open FilePath For Input Shared As FileHandle


Do While Not EOF(FileHandle)
Line Input #FileHandle, TextLine
cboItems.AddItem TextLine
Loop


Close #FileHandle


If cboItems.ListCount > 0 Then
cboItems.ListIndex = 0
Else
MsgBox "There are no items to list.", vbCritical, "Hmm!"
End If
Else
MsgBox "No such file:" & vbCrLf & FilePath, vbCritical, "Drat!"
End If
End Sub

 

[jlawson]

Thank you, ED! Super code once again!

Hello again.


My mistake. I assumed the combobox was on a VBA userform, not in a document
type of form. The first code sample presumes that a userform has a combobox
called "cboItems" and a command button called "cmdReadUsingVBABuiltin". As it
stands this code won't work for a combobox in a Word document.

I'm guessing that you created the combobox using the Controls toolbar?

I've never tried before to populate one of those using VBA (except just now
as a test). One way that seems as though it might work is this:

I'm assuming that you're creating a template (.dot) and that the combobox's
name shows as "ComboBox1" when you view its code.

With the template open, get to the code window for the document itself. I
think you can do this by right-clicking the combobox in design mode and
choosing "View code" or by going into VBA and double-clicking the template's
"ThisDocument" module.

In the left-hand drop-down select "Document". Then in the right-hand
drop-down select "New". This creates an empty event handler which runs each
time a new document is created based on the template.

Copy the code from the sample (excluding the "Private Sub ..." and "End Sub"
lines) into the Document_New procedure. Change any occurrences of "cboItems"
to "ComboBox1".

Remove the Document_Change procedure.

Exit design mode, protect the form and save it.

Then create a new document based on the template (either through "File/New"
or by double-clicking it in Explorer), and see if it has worked.

(If you are creating a document (.doc) then in the above steps select "Open"
rather than "New" from the right-hand drop-down. This creates an empty event
handler which runs each time the document is opened.)

Regards.

Ed