Ruth
Wed May 07 19:17:27 PDT 2008
On May 7, 7:50 pm, "Doug Robbins - Word MVP" <d...@REMOVECAPSmvps.org>
wrote:
> In such a case, you should probably use a userform with a combobox that is
> populated with data stored in another location.
>
> See the article "How to create a Userform" at:
>
>
http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm
>
> This routine loads a combobox 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.
> It assumes that the table has a header row which identifies the data in
> each column and that the order of the columns is Initials, Name, email
>
> On the UserForm, have a combobox (ComboBox1) 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,
> Dim 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:="c:\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
> ComboBox1.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 ComboBox1
> ComboBox1.List() = MyArray
> ' Close the file containing the client details
> sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
> End Sub
>
> Private Sub CommandButton1_Click()
> With ActiveDocument
> ComboBox1.BoundColumn = 2
> .Bookmarks("Name").Range.InsertBefore ComboBox1.Value
> ComboBox1.BoundColumn = 3
> .Bookmarks("email").Range.InsertBefore ComboBox1.Value
> End With
> End Sub
>
> The Initialize statement will populate the combobox 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 the
> bookmarks in the document.
>
> --
> 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
>
> "Ruth" <moocatmoo...@yahoo.com> wrote in message
>
> news:4f189df7-c694-4e08-9c1f-dc8088d0e94f@a70g2000hsh.googlegroups.com...
>
> > Many thanks for your help with my recent question. I'm hoping you
> > have time to answer one more:
> > you had said in your reply:
> > "Storing the names and addresses directly in the code this way is
> > acceptable if
> > there are only a few, and if they don't change very often. If that
> > isn't the
> > case, you don't want to have to edit the macro every time there's a
> > change. Then
> > you want to store the names and addresses somewhere else -- in a Word
> > document,
> > an Excel worksheet, a database, etc. -- and read that source when the
> > macro
> > runs. "
>
> > In the case of the macro I asked about, storing the code in the macro
> > should be fine...but for future reference, how would I store the names
> > & address in a Word document and have the macro 'call' that info?
>
> > thanks again!
> > Ruth
thanks again! I'm sure I'll need this sort of code in the near future.