Combobox that populates from outside source or a declared list val

[MariaV]

I have created a word document with fields for Techs to fill out online. In
each row they will choose the customer name, error detected, etc. Each table
will hold 25 lines, or 25 different customers. I would like to find an easy
way to duplicate a combobox (drop-down), so that each row will give them the
same choice of our customers.

I know the .additem, but I don't want to repeat that list 25 times
throughout the code. Ideally I would like someone else to maintain the
document after I have created it. I can show them how to go into the code,
but I prefer if they only have to modify one area. I tried to find info on
declaring a list string, but I am not sure if I am using the correct
terminology.

Connecting to an excel, access, or other document is a possibility, but I
would prefer if it was contained all in the same file (aka word doc). I use
to be comfortable with Visual Basic, however it was over 5 years ago and I
feel like I am starting all over again.


Thanks in advance,
Maria

 

[Jezebel]

Why not do the whole thing in Excel? You can use the Data > Validation
function to create the drop-downs, which is a heap easier than putting 25
drop-downs on each page of a Word document.

 

[Doug Robbins - Word MVP]

I don't know where the 25 times comes into it, but, this routine loads a
listbox
with client details stored in a table in a separate document (which makes it
easy to maintain with additions, deletions etc.), that document being saved
as Clients.Doc for the following code.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
saved Clients.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

To make it easy for you, the code has been written so that it will deal with
any number of clients and any number of details about each client. It
assumes that the first row of the table containing the client details is a
header row.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Jezebel]

I don't know where the 25 times comes into it,

Each table
will hold 25 lines, or 25 different customers.

 

[MariaV]

Yes that is correct, and a list box is not ideal because of space . . . and
is there anyway that I can avoid using any buttons? Even though the document
is going to be used by our Network techs, I want them to just open the file
like a regular word document and do a print and close.

Jezebel ~ I thought about doing it in Excel, but I made the mistake of
installing the 2007 version (my word and outlook is 2003) . . . I can't seem
to find the same functionality in 2007 as I could in 2003 and I don't have
time to play.

Thanks,
Maria

 

[bigceetwo]

Hello Doug,

I tried running your code but keep getting the error "Ärgument not
optional". It appears to be refering to the line [myitem.End =
myitem.End - 1]. Any suggestions?

Also, do you have a similar routine for an excel document?

Many thanks,

Biggy

 

[Doug Robbins - Word MVP]

I can't see why that should cause a problem. If you like, send me the file
that contains the data that you are trying to load into the listbox so that
I can check if there is something about it that might cause the problem.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Hello Doug,

I tried running your code but keep getting the error "Ärgument not
optional". It appears to be refering to the line [myitem.End =
myitem.End - 1]. Any suggestions?

Also, do you have a similar routine for an excel document?

Many thanks,

Biggy

 

[bigceetwo]

Hello Doug,

Thanks for your help.

I was using VB in Excel but when I switched to VB in Word all worked
well.

Thanks again.

biggy

Hello Doug,

I tried running your code but keep getting the error "Ärgument not
optional". It appears to be refering to the line [myitem.End =
myitem.End - 1]. Any suggestions?

Also, do you have a similar routine for an excel document?

Many thanks,

Biggy

I don't know where the 25 times comes into it, but, this routine loads a
listbox
with client details stored in a table in a separate document (which makes it
easy to maintain with additions, deletions etc.), that document being saved
as Clients.Doc for the following code.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
saved Clients.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

To make it easy for you, the code has been written so that it will dealwith
any number of clients and any number of details about each client. It
assumes that the first row of the table containing the client details is a
header row.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP