Trying to revive this question... Help :)

[Junkyard Engineer]

I have a list of question in 2 sheets within the same workbook.

Those questions are risk evaluation for projects but depending on 5
criterias, project risk evaluation is modulated in 3 categories : low,
medium and high risk. In the case of Low risk, the first sheet is skipped
altogether and only certain questions on sheet 2 must be answered.

For medium risk, certain question of sheet 1 and 2 must be answered

For High risk, all questions of both sheets must be answered.

For low and medium risk, the questions are not necessarely sequential, i.e.
Q1, 3, 34, 45, etc...

Once answered, I already have a vba code taking all High risk evaluation
questions and copy them into a third sheet which is the contingency planning
for those risk.

MY PROBLEM / QUESTION : When the Evaluation Agent comes in the workgroup, I
want to have a first page interface where he must first answer those 5
questions to evaluate which category the project falls in. Then
automatically or by the push of a button, a questionnaire will popup asking
all questions identified in the appropriate risk group. Once the
questionnaire is answered, the vba code for contingency planning is
automatically executed and the agent is presented with this 3rd sheet.


Can this be done ? (I suppose yes) How ? Is there already an example
somewhere where I can elaborate and taylor to my need ? Of course, results
should be copied into each appropriate cells. Down the road, I plan to print
a summary of those questions/answers but I'm not there yet.

Thanks for any help you can provide me !

 

[Gareth]

There's many ways to tackle this. Here's one suggestion to point you in
the right direction.

My idea would be to use two forms (or even one). Form 1 would be used to
evaluate your initial risk level. This would set a public variable to 1,
2 or 3. Each question should have 1 2 or 3 against it in your Excel
worksheet.

You then loop through all of your questions. If the question has a risk
level equal to or less than your global risklevel variable then you
should, launch the Question form. Each time the user answers you store
their response on the appropriate sheet.

My reason for using this approach is that you can add and remove
questions as you see fit without having to change your code. You may
need to handle different types of questions - e.g. Boolean response,
Options, Strings, Dates etc. You could have a different form for each type.

Below I have sketched out the kind of approach I would use - it's very
incomplete. I haven't managed the answer writing properly for example.


'assumptions - column 1 of the questions

A B C D
Q1 My question RiskLevel AnswerColumn


Public RiskLevel as integer
Public MyAnswer as String

Sub StartQuestioning()

'Launch Evaluation Form
frmEvaluateInitialRisk.Show
'this process should set
RiskLevel = 1, 2 or 3

'Now loop through all the questions on your worksheets
with thisworbook
for each sh in .sheets
with sh
for i = 1 to .usedrange.rows.count
'check this row is a question
if left$(.cells(i,1),1) = "Q" then
if .cells(i,3) <=RiskLevel then

myAnswer = ""
frmAskQuestion.Display .cell(i,2)
'note that this requires a funciton in the form
'called 'display' that returns a string in myAnswer
'Function requires Question in argument
If myAnswer = "" then
'assume user aborted
exit sub
else
.cells(i,4) = myanswer
end if
else
.cells(i,4) = "NOT REQUIRED"
end if

end if
next i
end with
Next sh

End Sub


HTH,
Gareth

 

[Junkyard Engineer]

Thanks ! That certainly is a good start.

There's many ways to tackle this. Here's one suggestion to point you in
the right direction.

My idea would be to use two forms (or even one). Form 1 would be used to
evaluate your initial risk level. This would set a public variable to 1, 2
or 3. Each question should have 1 2 or 3 against it in your Excel
worksheet.

You then loop through all of your questions. If the question has a risk
level equal to or less than your global risklevel variable then you
should, launch the Question form. Each time the user answers you store
their response on the appropriate sheet.

My reason for using this approach is that you can add and remove questions
as you see fit without having to change your code. You may need to handle
different types of questions - e.g. Boolean response, Options, Strings,
Dates etc. You could have a different form for each type.

Below I have sketched out the kind of approach I would use - it's very
incomplete. I haven't managed the answer writing properly for example.


'assumptions - column 1 of the questions

A B C D
Q1 My question RiskLevel AnswerColumn


Public RiskLevel as integer
Public MyAnswer as String

Sub StartQuestioning()

'Launch Evaluation Form
frmEvaluateInitialRisk.Show
'this process should set
RiskLevel = 1, 2 or 3

'Now loop through all the questions on your worksheets
with thisworbook
for each sh in .sheets
with sh
for i = 1 to .usedrange.rows.count
'check this row is a question
if left$(.cells(i,1),1) = "Q" then
if .cells(i,3) <=RiskLevel then

myAnswer = ""
frmAskQuestion.Display .cell(i,2)
'note that this requires a funciton in the form
'called 'display' that returns a string in myAnswer
'Function requires Question in argument
If myAnswer = "" then
'assume user aborted
exit sub
else
.cells(i,4) = myanswer
end if
else
.cells(i,4) = "NOT REQUIRED"
end if

end if
next i
end with
Next sh

End Sub


HTH,
Gareth

 

[Gareth]

You're welcome.

Thanks ! That certainly is a good start.