Re: FORMDROPDOWN Problems

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.


.