FORMDROPDOWN Problems

[Stumped_In_Hbg]

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 sheet 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.

 

[Doug Robbins]

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]

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.