Re: FORMDROPDOWN Problems

Thank you so much. I now know where to concentrate my attacks.

It looks like I already had all the pieces (both links were among the
mountain I printed off), but was unable to see how they tie together. I
literally couldn't see the mountain for the trees.




"Doug Robbins" wrote:

> You cannot sensibly do this without use vba code. And if the network
> nannies won't accept the following method of doing it, I would be telling
> them to stick their job. If the templates are stored in the user template
> directory, the macro security level can be left at high.
>
> Your templates should use userforms with a combobox rather than formfields.
>
> See the article "How to create a Userform" at:
>
> http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm
>
>
> This routine loads a listbox (it could also be a combobox) with client
> details stored in a table in a separate
> document (which makes it easy to maintain with additions, deletions etc. and
> that same table would be used for all templates), that document being saved
> as Clients.Doc for the following code.
>
> 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
>
> To handle the distribution of the templates, see the article "Distributing
> macros to other users" at:
>
> http://word.mvps.org/FAQs/MacrosVBA/DistributeMacros.htm
>
> Don't be put off by the title.
> --
> 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
> "Stumped_In_Hbg" <Stumped_In_Hbg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:550829B4-57C0-468F-A80D-3B1E3D56ABFA@xxxxxxxxxxxxxxxx
> >I think I've been looking so long and so hard, that I feel like I'm missing
> > something really obvious here.
> >
> > I've been researching this problem for 3 (plus) days now. I've been to
> > something in the area of 40 web sites, printed 3 reams of paper, been *all
> > over* the Word help file, even searched this discussion group area every
> > possible way I can think of--but right now, I truly believe I'd make more
> > progress studying the principals of warp drives.
> >
> > And it's really driving me nuts because damn near *every* other problem
> > I've
> > come up against, I've been able to solve in a lot less time, and with a
> > lot
> > less wasted effort. So basically, I'm not used to failure when it comes
> > to
> > finding my own answers. If this were Excel, I can think of about 5 or 6
> > different ways to do this but alas, it's not Excel and I have to admit
> > defeat
> > (for now).
> >
> > Here's what's got me stumped...
> >
> > At work, we have something on the order of 40 to 50 templates stored on a
> > network share which our users have to access many times each day. In most
> > of
> > those forms, are drop down fields to select supervisors names and numbers.
> >
> > Currently, I have to update each of those drop downs *every* time staff
> > changes. And generally, that's about once a month or so. If I miss any,
> > something as seemingly minor as an invalid POC, could result in a decision
> > being reversed where it should not be.
> >
> > What I'm trying to do, is to be able to auto-populate those drop downs
> > from
> > data maintained in a (one, not 30 or 40) single data source using either
> > Excel, or Access, so that our POC information is always correct and up to
> > date (assuming of course the central database is updated in a timely
> > fashion).
> >
> > Network policy forbids the use of macros in the general user base so VBA
> > is
> > out. Most of our users are about as knowledgeable in Word, as... Well,
> > let's
> > just say I've seen middle school students who are more adept.
> >
> > Method 1-
> > I've tried AUTOTEXTLIST, DATABASE, and DDE field codes so far, and so far,
> > the only one which seems to work somewhat, is the DDE field code.
> > However,
> > it requires the Excel *** to be opened to get the requested data. With
> > our
> > user skill level, this really is not a viable option.
> >
> > Method 2-
> > The only solution I've been able to conceive of, is to have a copies of
> > the
> > templates *with* macros, do the updates, then copy themselves over the
> > existing active templates. Using a combination of tools, I believe I can
> > get
> > Word to kick off when needed, running the auto-updating macros on the
> > master
> > templates. Then I should be able to write just the template portions to
> > working copies thus achieving the same results.
> >
> > But someone please tell me method 1 will in fact work so I don't have to
> > do
> > it in the VBA procedure...
> >
> > Thanks in advance.
>
>
>
.