Using drop down lists to select specific criteria

[Diggles1972]

Hi all,

I am trying to create within Word, a drop down box that would have a list of
subject areas that would be tought (not a problem, lol). The problem occurs
that I wish, once the subject has been chosen (e.g. Math, Science, Drawing,
etc), that in another drop down box further down the form only the grading
criteria for that subject area appears and can be selected. For example, if I
use the three subjects I have suggested, each subject may have criterias
named P1 and P2 with a requrement for each specific criteria so,

Maths Science Drawing
P1 'Do this' P1 'Do this instead' P1 'Don't do
this'
P2 'A maths criteria' P2 'A science criteria' P2 'A drawing
criteria'


When the user selects the subject area on the criteria that are relevant to
that subject appear in the second drop-down list so that one may be selected.

I'm not even sure if this is possible although I have been told that it is.

Please help me, lol. Thank you in anticipation.

 

[Anne Troy]

I didn't say it would be easy, did I? Try this:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=5

*******************
~Anne Troy

www.OfficeArticles.com

 

[Doug Robbins]

It certainly is possible. For just a couple of levels like that, I do it by
storing the subject and the grading criteria in a separate document with the
subjects in individual cells in the first column of a two column table and
the grading criteria for each subject as separate paragraphs in the cell in
the second column adjacent to the subject to which they are applicable.
Like

Maths P1 Maths criteria 1
P2 another maths criteria
Science P1 Science criteria 1
P2 Science criteria 2

etc.

Then, if you have two comboboxes on your userform, one named cmbSubject and
the other cmbCriteria, if you have the following code in the form, when the
form is initialised, the cmbSubject combobox will be loaded with the
Subjects and when a subject is selected, the cmbCriteria combobox will be
loaded with the associated criteria:

Option Explicit
Dim sourcedoc As Document, i As Integer, j As Integer, Subject As Range,
Criteria As Range

Private Sub cmbSubject_Change()
'Remove any existing items in cmbCriteria
If cmbCriteria.ListCount >= 1 Then
For j = cmbCriteria.ListCount To 1 Step -1
cmbCriteria.RemoveItem (j)
Next j
End If
' Modify the path in the following line so that it matches where you
save formdata.doc
Application.ScreenUpdating = False
' Open the file containing the formdata details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\formdata.doc")
j = cmbSubject.ListIndex + 2
For i = 1 To sourcedoc.Tables(1).Cell(j, 2).Range.Paragraphs.Count
Set Criteria = sourcedoc.Tables(1).Cell(j,
2).Range.Paragraphs(i).Range
Criteria.End = Criteria.End - 1
cmbCriteria.AddItem Criteria
Next i

End Sub

Private Sub UserForm_Initialize()
' Modify the path in the following line so that it matches where you
save formdata.doc
Application.ScreenUpdating = False
' Open the file containing the formdata
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\formdata.doc")
For i = 2 To sourcedoc.Tables(1).Rows.Count
Set Subject = sourcedoc.Tables(1).Cell(i, 1).Range
Subject.End = Subject.End - 1
cmbSubject.AddItem Subject
Next i
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

p.s. I don't have access at the moment to projects in which I have used
this technique so I have just put it together on the spot and have not
actually tested it.


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

 

[Diggles1972]

Thank you so much. That information has been really useful as I can keep
everything in one file. Could I impose upon you one more time please and ask
how I can extend this. I have, basically, no knowledge of VBA, although I can
interpret and understand that which is being coded in the link you gave me.
My problem is the initial compiling of the code.

The extension I am now looking for is to be able to put a paragraph of text
in for each criteria entry. I will employ the technique currently shown to
select the subject area required (Dropdown1) that will then lead to the
criteria selection (Dropdown2). When the criteria has been selected it needs
a paragraph of text to appear next to it which will be different in content
depending upon the criteria selected. My belief, from my limited
understanding of VBA, is that a Text Field is needed. Would this be the case
and if so, how would I call that up following the selection of the criteria?

Many thanks for your help, you've helped an ignorant Mechanical Engineering
Lecturer no end.

 

[Diggles1972]

Thank you Doug. I appreciate the information. I am going to try Anne's route
if you don't mind as it appears to keep everything within one file rather
than having several (unless I have misunderstood your reply). This needs to
be idiot proof as it is going to be used by Mechanical Engineering Lecturers
who aren't necessarily IT literate. I can comprehend the need to keep certain
files together for a process to work but others probably won't. Therefore, if
I can keep everything in one file then they can't muck it up.....at least
that's the intention, lol. But I thank you anyway for your time with this
matter.

Best regards.

Digby Eade.

 

[Doug Robbins]

The advantages of my method are:

It does not require the use of documents that are protected, though that may
not be an issue for your application.

It does not require messing around with the code if you need to add or
delete subjects and their criteria or add or remove criteria to/from an
existing subject.

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