AutoTextList? UserForm? Macros? Not sure which way to go...

[Beth Brooks]

Hi!

I want to create a Word document that lets the user choose one reviewer from
a list for each of twelve categories of review (eg, choose the reviewer for
Clinical, Safety, Medical, etc.) and then pulls each of the chosen reviewers'
most current data (ie, name, degrees, title, department, etc.) from an Excel
spreadsheet to populate a sign-off page. Another group is responsible for
maintaining the spreadsheet. I want to avoid the need to maintain it on our
end, if at all possible.

The current total list of reviewers is 32 people, but each reviewer can only
sign off in one or two categories, so there are 12 short lists, really. I
thought this would be a great job for AutoTextList, but I need to be sure the
autotext entries are always the most current data from the spreadsheet and I
want that part to be automated.

Would love to hear your suggestions!

Beth

 

[Charles Kenyon]

AutoTextList selects from AutoText -- which is not part of any Excel
spreadsheet, it is in a Word template.
Looks more like a job for mailmerge to me.
--
Charles Kenyon

Word New User FAQ & Web Directory: http://addbalance.com/word

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide) http://addbalance.com/usersguide


--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

 

[Beth Brooks]

Charles,

I thought about that, but how would it work? Each time we create a new
signature form, the combination of reviewers is different. How do I allow the
user to determine which one of the five Clinical reviewers gets merged, which
of the three Safety reviewers gets merged, etc.?

Beth

 

[Jay Freedman]

Charles is right that there's no connection between the Excel sheet and
AutoText (it might be possible to create the AutoText from the sheet with a
macro, but it's more work than it's worth). I don't see how a mail merge
would work, either.

I'll suggest a userform with 12 dropdowns (combo boxes), one for each
category. I'll assume you can get (or make) an appropriate named range in
the spreadsheet for each category; if a reviewer appears in two categories,
his or her entire row should be duplicated. Then you can adapt the code in
http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm to load each
dropdown from the corresponding named range in the Userform_Initialize
procedure. By using arrays to hold the dropdown names and the range names in
the proper order, you can write the procedure as a loop instead of repeating
everything 12 times.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Peter Jamieson]

How about a bit more adaptation of the macro, to would avoid the need to
have or create named ranges, basically by issuing SQL that sorts the data by
reviewer within category, e.g. something like
SELECT category, reviewername FROM `Sheetname` ORDER BY 1,2

(This assumes you can use a sheet name such as Sheet1$ instead of a range
name as the "table name", which is certainly possible in ADO).

(Or use one SELECT to get the categories, then a second SLECT to get the
names for each category.)

Peter Jamieson

 

[Beth Brooks]

Jay,

Thanks! That sounds like the solution I was looking for. I followed
everything up to this point:

By using arrays to hold the dropdown names and the range names in
the proper order, you can write the procedure as a loop instead of repeating
everything 12 times.

Can you please elaborate or direct me to a resource where I can research
the use of arrays? (I'm an editor, not a developer, so I need something
basic). Thanks again!

Beth

 

[Jay Freedman]

Hi Beth,

Yes, it's a bit complicated if you don't have a programming background.

Here's a sample of how you could change the example given at
http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm to use arrays. I
inserted comments ('''' Added '''' and '''' Changed '''') to bracket the
differences. The explanation follows the code.

Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

'''' Added ''''
Dim RangeNames As Variant
Dim ComboNames As Variant
Dim idx As Integer

RangeNames = Array("Class_1", "Class_2")
ComboNames = Array("ComboBox1", "ComboBox2")
'''' Added ''''

' Open the database
Set db = OpenDatabase("C:\Docs\Book1.xls", False, False, "Excel 8.0")

'''' Added ''''
For idx = 0 To UBound(RangeNames)
'''' Added ''''

' Retrieve the recordset
'''' Changed ''''
Set rs = db.OpenRecordset("SELECT * FROM `" & RangeNames(idx) & "`")
'''' Changed ''''

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
'''' Changed ''''
Me.Controls(ComboNames(idx)).ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
Me.Controls(ComboNames(idx)).Column = rs.GetRows(NoOfRecords)
'''' Changed ''''

'''' Added ''''
' Display the first item in the list
Me.Controls(ComboNames(idx)).ListIndex = 0
'''' Added ''''

' Cleanup
rs.Close

'''' Added ''''
Next idx
'''' Added ''''

db.Close

Set rs = Nothing
Set db = Nothing

End Sub

In the first added segment, I declare two Variant variables, each of which
can hold an array. Using the Array() function, I assign RangeNames to hold
the list of named ranges that exist in the Excel spreadsheet. In the same
order, I assign ComboNames to hold the names of the combo boxes that will be
filled with the data from those ranges -- that is, the data from range
Class_1 will go into ComboBox1, and so on. You would list the 12 named
ranges from your spreadsheet (whatever names they have) and the 12 combo
boxes on your userform, each in quotes and separated by commas.

The next change is to insert a For...Next loop around the code that reads
the spreadsheet and fills the combo boxes. By using the UBound function, it
automatically loops once for each name in RangeNames.

The OpenRecordset call is changed to use the current range name during each
pass through the loop.

The expression Me.Controls(ComboNames(idx)) refers to the combo box that
matches the current range name during each pass through the loop.

Post back if you hit any more roadblocks.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.