Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to revive this question... Help :)
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 ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to revive this question... Help :)
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 wrote: 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 ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to revive this question... Help :)
Thanks ! That certainly is a good start.
"Gareth" a écrit dans le message de news: ... 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 wrote: 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 a summary of those questions/answers but I'm not there yet. Thanks for any help you can provide me ! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to revive this question... Help :)
You're welcome.
Junkyard Engineer wrote: Thanks ! That certainly is a good start. "Gareth" a écrit dans le message de news: ... 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 wrote: 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 a summary of those questions/answers but I'm not there yet. Thanks for any help you can provide me ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|