Cascading/Conditional Dropdowns

[dattagal]

Hi All

I am new to this discussion group - thanks for providing it.

I have a situation similar to another user who wanted to programmatically
populate a dropdown based on the results of the previous dropdown. The user
was provided the following code:

Sub PopulateSubCat()
Dim oFld As FormFields
Set oFld = ActiveDocument.FormFields
Select Case oFld("Cat").Result
Case Is = "A"
With oFld("SubCat").DropDown
.ListEntries.Clear
.ListEntries.Add "Apples"
.ListEntries.Add "Apricots"
.Value = 1
End With
Case Is = "B"
With oFld("SubCat").DropDown
.ListEntries.Clear
.ListEntries.Add "Blueberries"
.ListEntries.Add "Bananas"
.Value = 1
End With
Case Is = "C"
'Etc.
End Select
End Sub

My situation is somewhat different however in that I need to
programmatically populate the first dropdown as well.

The first dd I need to populate is for group offices. Word limits the
entries in a dropdown (as I am sure you well know) to 25 and there are approx
30 group offices.

The second dropdown I want to populate is sales reps and I want to populate
the dd based on the selection in the group office dd. I also have a third dd
which will be account assistants which I would like populated based on the
group office selection as well.

For example, if the user selects Atalnta from the (programmatically
populated) Group Office dd, I want the Sales Rep dd and Account Assistants
dropdowns to populate with sales reps and account assistants in the Atanta
Group Office.

Also, I have a Cost Center field I would like to have populated with the
cost center of the group office selected in the first dropdown).

In other words, on selection of the group office I want the sales reps for
that group office to display in the Sales Reps dd, account assistants for
that group office to display in the Account Assistants dropdown and the cost
center for that group to appear in the Cost Center field.

Is it going to take a ton of code to accomplish this? Not that mind a
little work mind you and I love VBA (although I have never used it in Word)
but I am a little intimitated b/c my experience with VBA is in Access and
somewhat limited in that I use basically the same code over and over again in
different scenarios.

Anyway, any help with this woud be greatly appreciated.

Oh, sorry for the long post - I just wanted to make sure I was clear on what
I want to do.

btw: I believe it is Office 2000 I am using at work (I just started 3 weeks
ago so I am not 100% sure but I believe it is 2k)

Thanks so much

Dattagal

 

[Jay Freedman]

There's one little fact that forces a large change: There's no way
you'll ever get more than 25 group offices listed in a FormField
dropdown. It's a hard-coded limit, as is the 50 characters per item
limit. :-b

That means you'll have to make a userform (custom dialog) so you can
use the more capable combo boxes that are available there. To get
started, read
http://www.word.mvps.org/FAQs/Userforms/CreateAUserForm.htm. Learn how
to create a basic userform and how to transfer values from it to
bookmarks in the active document.

The code that goes into the userform to populate the combo boxes will
look a lot like what you have for form fields, but more of it because
you have more fields.

As an example, if you create a userform with three combo boxes that
you name cbGroupOffice, cbSalesRep and cbAssistant; a text box named
tbxCostCtr; and an OK button, the code to populate the box will look
something like this (but with lots more .AddItem lines):

Private Sub cbGroupOffice_Change()
Select Case cbGroupOffice.ListIndex
Case 0
With cbSalesRep
.Clear
.AddItem "Barney"
.AddItem "Betty"
.ListIndex = 0
End With
With cbAssistant
.Clear
.AddItem "Mindy"
.AddItem "Mork"
.ListIndex = 0
End With
tbxCostCtr.Text = "0100"
Case 1
With cbSalesRep
.Clear
.AddItem "Fred"
.AddItem "Wilma"
.ListIndex = 0
End With
With cbAssistant
.Clear
.AddItem "Jack"
.AddItem "Jill"
.ListIndex = 0
End With
tbxCostCtr.Text = "0200"
Case 2
With cbSalesRep
.Clear
.AddItem "George"
.AddItem "Gracie"
.ListIndex = 0
End With
With cbAssistant
.Clear
.AddItem "Clark"
.AddItem "Lois"
.ListIndex = 0
End With
tbxCostCtr.Text = "0300"
End Select
End Sub

Private Sub UserForm_Initialize()
With cbGroupOffice
.MatchRequired = True
.AddItem "Atlanta"
.AddItem "Boston"
.AddItem "Denver"
.ListIndex = 0
End With

cbSalesRep.MatchRequired = True
cbAssistant.MatchRequired = True
End Sub

There will also be a subroutine named btnOK_Click() with code to move
the contents of the boxes to bookmarks in the document. Because that
depends more on how you set up your document, I can't be specific, but
the article I cited will give you a starting point.

Once you get this under your belt, there are better ways to handle the
population of the boxes. If you put all the names and cost centers in
the code like this, you have to edit and redistribute the macro every
time there's even a tiny change in the data. You can recode this so
the data are read from some external document -- a Word document, an
Excel spreadsheet, a table in an Access database, Active Directory,
etc.

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

 

[Doug Robbins - Word MVP]

As Jay has suggested, you should use a userform instead of formfields in a
protected document.

I usually do this by storing (to use your example) the offices, one to a
row in the first column of a two column table and sales reps applicable to
each office in individual paragraphs in the adjacent cell in the second
column of a document. Then, on loading the userform, the first combobox is
populated with the offices by iterating throught the cells of the first
column in the table and on selecting an item in that combobox. the
..ListIndex property of the combobox is used to determine the cell from which
the sales reps are added to the second combobox. By doing it this way, the
data for the combo boxes is stored in an external document where it can
easily be maintained instead of having to go in an alter the code whenever
there is a change in the data.

For an example of the code to do this, see:

http://groups.google.com/group/micr...doug's+combo+box+code&rnum=1#bf86a5657fe2c34e


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

 

[dattagal]

Thanks so much for the your prompt replies - they are truly appreciated.
I will figure out which of these is the most viable solution for me and get
to work on it.

Thanks so much!