|
Programming a Directed Flow Chart
I'd like to set up a spreadsheet whe
- Question One is asked, and yes or no is selected from a drop-down pickbox. - If "Yes", Question Two appears (with a "Yes/No" box); if "No", Question Four appears (with a "Yes/No" box); - For Question Two, if "Yes", Question Four appears, and if "No", Question Three appears (with Yes/No boxes)... and etc., etc. for seven questions. Then, based on the answers, I want to display different solutions. There are twelve different outcomes. Thoughts? (I know how to do the pick boxes, but that's about it. I was thinking I could do the appearing bit with conditional formatting, but what do I know?) |
Programming a Directed Flow Chart
turn on the macro recorder and create the data validation for a cell using
the list option with your choices. Turn of the macro recorder and look at the code. Now use this as the model for creating the pick boxes. Use the worksheet_Change event to react to a selection for each pickbox and create the next one, positioning the user at that cell. Another way would be to create all the boxes and as choices are made, use the change event to delete the inappropriate ones based on the selection. Position the user at the next appropriate one. specific code requires specific knowledge. statements like "a drop-down pickbox" and "Question Two appears" and "and etc., etc. for seven questions. " are not statments that provide the requisite detail. -- Regards, Tom Ogilvy "RJB" wrote: I'd like to set up a spreadsheet whe - Question One is asked, and yes or no is selected from a drop-down pickbox. - If "Yes", Question Two appears (with a "Yes/No" box); if "No", Question Four appears (with a "Yes/No" box); - For Question Two, if "Yes", Question Four appears, and if "No", Question Three appears (with Yes/No boxes)... and etc., etc. for seven questions. Then, based on the answers, I want to display different solutions. There are twelve different outcomes. Thoughts? (I know how to do the pick boxes, but that's about it. I was thinking I could do the appearing bit with conditional formatting, but what do I know?) |
Programming a Directed Flow Chart
Like I said, I know how to do the Data Validation/Choose from Drop-Down
thing... That's not the issue. I don't know how to be more specific without showing you the Visio Chart I'm trying to automate, but here's another try: Q1. Are you, or have you ever been, a Communist? If "Yes", go to Question 2 If "No", go to Question 4 Q2. Are you ready to renounce your Communism? If "Yes", go to Question 4 If "No", go to Question 3 Q3. Are you sure? If "Yes", go to Question 5 If "No", go to Question 4 .... and so on and so on. The trick is, I don't want the question to appear until it's time to be answered. In other words, I only want one question onscreen at a time. I suspect Excel can handle this? "Tom Ogilvy" wrote: turn on the macro recorder and create the data validation for a cell using the list option with your choices. Turn of the macro recorder and look at the code. Now use this as the model for creating the pick boxes. Use the worksheet_Change event to react to a selection for each pickbox and create the next one, positioning the user at that cell. Another way would be to create all the boxes and as choices are made, use the change event to delete the inappropriate ones based on the selection. Position the user at the next appropriate one. specific code requires specific knowledge. statements like "a drop-down pickbox" and "Question Two appears" and "and etc., etc. for seven questions. " are not statments that provide the requisite detail. -- Regards, Tom Ogilvy "RJB" wrote: I'd like to set up a spreadsheet whe - Question One is asked, and yes or no is selected from a drop-down pickbox. - If "Yes", Question Two appears (with a "Yes/No" box); if "No", Question Four appears (with a "Yes/No" box); - For Question Two, if "Yes", Question Four appears, and if "No", Question Three appears (with Yes/No boxes)... and etc., etc. for seven questions. Then, based on the answers, I want to display different solutions. There are twelve different outcomes. Thoughts? (I know how to do the pick boxes, but that's about it. I was thinking I could do the appearing bit with conditional formatting, but what do I know?) |
Programming a Directed Flow Chart
Yes, Excel can handle it. But you said you are trying to automate a Visio
Chart. If you want to do it in Excel, then use a Userform http://maths.sci.shu.ac.uk/units/ioa/ <- Excel VBA tutorial http://j-walk.com/ss/excel/tips/tip84.htm http://www.contextures.on.ca/xlUserForm01.html http://www.dicks-blog.com/excel/2004...g_userfor.html http://support.microsoft.com/default...b;en-us;829070 How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel XL97: How to Use a UserForm for Entering Data (Q161514) http://support.microsoft.com/?id=161514 XL2000: How to Use a UserForm for Entering Data (Q213749) http://support.microsoft.com/?id=213749 http://support.microsoft.com/kb/213574/en-us XL2000: How to Display a UserForm Whose Name Is in a Variable Here are some other sources of information: http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm Lesson 11: Creating a Custom Form Excerpted from Microsoftยฎ Excel 97 Visual Basicยฎ Step by Step. http://support.microsoft.com/?id=168067 File Title: Microsoft(R) Visual Basic(R) for Applications Examples for Controlling UserForms in Microsoft Excel 97 File Name: WE1163.EXE File Size: 161742 bytes File Date: 05/08/97 Keywords: kbfile Description: This Application Note is an introduction to manipulating UserForms in Microsoft Excel 97. It includes examples and Microsoft Visual Basic for Applications macros that show you how to take advantage of the capabilities of UserForms and use each of the ActiveX controls that are available for UserForms Peter Aiken Articles: Part I http://msdn.microsoft.com/library/en...FormsPartI.asp Part II http://msdn.microsoft.com/library/en...ormsPartII.asp http://support.microsoft.com/default...b;en-us;213732 XL2000: Using the LoadPicture Function with an Image Control -- Regards, Tom Ogilvy "RJB" wrote: Like I said, I know how to do the Data Validation/Choose from Drop-Down thing... That's not the issue. I don't know how to be more specific without showing you the Visio Chart I'm trying to automate, but here's another try: Q1. Are you, or have you ever been, a Communist? If "Yes", go to Question 2 If "No", go to Question 4 Q2. Are you ready to renounce your Communism? If "Yes", go to Question 4 If "No", go to Question 3 Q3. Are you sure? If "Yes", go to Question 5 If "No", go to Question 4 ... and so on and so on. The trick is, I don't want the question to appear until it's time to be answered. In other words, I only want one question onscreen at a time. I suspect Excel can handle this? "Tom Ogilvy" wrote: turn on the macro recorder and create the data validation for a cell using the list option with your choices. Turn of the macro recorder and look at the code. Now use this as the model for creating the pick boxes. Use the worksheet_Change event to react to a selection for each pickbox and create the next one, positioning the user at that cell. Another way would be to create all the boxes and as choices are made, use the change event to delete the inappropriate ones based on the selection. Position the user at the next appropriate one. specific code requires specific knowledge. statements like "a drop-down pickbox" and "Question Two appears" and "and etc., etc. for seven questions. " are not statments that provide the requisite detail. -- Regards, Tom Ogilvy "RJB" wrote: I'd like to set up a spreadsheet whe - Question One is asked, and yes or no is selected from a drop-down pickbox. - If "Yes", Question Two appears (with a "Yes/No" box); if "No", Question Four appears (with a "Yes/No" box); - For Question Two, if "Yes", Question Four appears, and if "No", Question Three appears (with Yes/No boxes)... and etc., etc. for seven questions. Then, based on the answers, I want to display different solutions. There are twelve different outcomes. Thoughts? (I know how to do the pick boxes, but that's about it. I was thinking I could do the appearing bit with conditional formatting, but what do I know?) |
Programming a Directed Flow Chart
Not automate a Visio Chart per se... I have a flow chart, and I'd like to
have a way to automate it so people can load the file on their computer, answer the questions, and be guided to the correct solution, without having to muck about with following a drawn out decision tree. That works for some people, but not others. I will try your suggestions tomorrow. Thanks. |
Programming a Directed Flow Chart
I looked at a bunch of the links you sent, and I dont know that user forms
are the way to go. Correct me if Im wrong, but it appears that user forms are to COLLECT and GATHER information. What Im trying to accomplish is to have users answer a standard set of up to seven yes/no questions that will guide them to one of nine specific solutions. Id like the screen to only show one question at a time, with a drop-down box for yes or no. Then, depending on their answer, the next question appears. As youll see below, its a pretty serpentine matrix of answers. I have listed the entire decision tree below. LEGEND: Following each question (Q#:) is N or Y; the = shows what question it goes to next. In other words, the first one means: If the answer to Question 1 is No, ask Question Four. If that answer is No, supply Solution A. Taken together the bottom two means, If the answer to Question One is Yes, ask Question Two. If the answer to Question Two is No, ask Question Three. If the answer to Question Three is No, ask Question Four. If the answer to Question Four is Y, ask Question Five. If the answer to Question Five is No, ask Question Six. If the answer to Question Six is No, provide Solution E; if the answer to Question Six is Yes, provide Solution D. Q1:N=Q4:N=Solution A' Q1:N=Q4:Y=Q5:Y=Solution A' Q1:N=Q4:Y=Q5:N=Solution B' Q1:Y=Q2:Y=Q4:N=Q6:Y=Solution A' Q1:Y=Q2:Y=Q4:N=Q6:N=Solution C' Q1:Y=Q2:Y=Q4:Y=Q5:Y=Q6:Y=Solution A' Q1:Y=Q2:Y=Q4:Y=Q5:Y=Q6:N=Solution C' Q1:Y=Q2:Y=Q4:Y=Q5:N=Q6:Y=Solution D' Q1:Y=Q2:Y=Q4:Y=Q5:N=Q6:N=Q7:Y=Solution E' Q1:Y=Q2:Y=Q4:Y=Q5:N=Q6:N=Q7:N=Solution F' Q1:Y=Q2:N=Q3:Y=Q4:Y=Q5:N=Q6:Y=Solution D' Q1:Y=Q2:N=Q3:Y=Q4:Y=Q5:N=Q6:N=Q7:Y=Solution E' Q1:Y=Q2:N=Q3:Y=Q4:Y=Q5:N=Q6:N=Q7:N=Solution F' Q1:Y=Q2:N=Q3:Y=Q4:Y=Q5:Y=Q6:Y=Solution G' Q1:Y=Q2:N=Q3:Y=Q4:N=Q6:Y=Solution G' Q1:Y=Q2:N=Q3:Y=Q4:N=Q6:N=Solution H' Q1:Y=Q2:N=Q3:N=Q4:N=Q6:Y=Solution J' Q1:Y=Q2:N=Q3:N=Q4:N=Q6:N=Solution E' Q1:Y=Q2:N=Q3:N=Q4:Y=Q5:Y=Q6:Y=Solution J' Q1:Y=Q2:N=Q3:N=Q4:Y=Q5:Y=Q6:N=Solution E' Q1:Y=Q2:N=Q3:N=Q4:Y=Q5:N=Q6:N=Solution E' Q1:Y=Q2:N=Q3:N=Q4:Y=Q5:N=Q6:Y=Solution D' "Tom Ogilvy" wrote: Yes, Excel can handle it. But you said you are trying to automate a Visio Chart. If you want to do it in Excel, then use a Userform http://maths.sci.shu.ac.uk/units/ioa/ <- Excel VBA tutorial http://j-walk.com/ss/excel/tips/tip84.htm http://www.contextures.on.ca/xlUserForm01.html http://www.dicks-blog.com/excel/2004...g_userfor.html http://support.microsoft.com/default...b;en-us;829070 How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel XL97: How to Use a UserForm for Entering Data (Q161514) http://support.microsoft.com/?id=161514 XL2000: How to Use a UserForm for Entering Data (Q213749) http://support.microsoft.com/?id=213749 http://support.microsoft.com/kb/213574/en-us XL2000: How to Display a UserForm Whose Name Is in a Variable Here are some other sources of information: http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm Lesson 11: Creating a Custom Form Excerpted from Microsoftยฎ Excel 97 Visual Basicยฎ Step by Step. http://support.microsoft.com/?id=168067 File Title: Microsoft(R) Visual Basic(R) for Applications Examples for Controlling UserForms in Microsoft Excel 97 File Name: WE1163.EXE File Size: 161742 bytes File Date: 05/08/97 Keywords: kbfile Description: This Application Note is an introduction to manipulating UserForms in Microsoft Excel 97. It includes examples and Microsoft Visual Basic for Applications macros that show you how to take advantage of the capabilities of UserForms and use each of the ActiveX controls that are available for UserForms Peter Aiken Articles: Part I http://msdn.microsoft.com/library/en...FormsPartI.asp Part II http://msdn.microsoft.com/library/en...ormsPartII.asp http://support.microsoft.com/default...b;en-us;213732 XL2000: Using the LoadPicture Function with an Image Control -- Regards, Tom Ogilvy "RJB" wrote: Like I said, I know how to do the Data Validation/Choose from Drop-Down thing... That's not the issue. I don't know how to be more specific without showing you the Visio Chart I'm trying to automate, but here's another try: Q1. Are you, or have you ever been, a Communist? If "Yes", go to Question 2 If "No", go to Question 4 Q2. Are you ready to renounce your Communism? If "Yes", go to Question 4 If "No", go to Question 3 Q3. Are you sure? If "Yes", go to Question 5 If "No", go to Question 4 ... and so on and so on. The trick is, I don't want the question to appear until it's time to be answered. In other words, I only want one question onscreen at a time. I suspect Excel can handle this? "Tom Ogilvy" wrote: turn on the macro recorder and create the data validation for a cell using the list option with your choices. Turn of the macro recorder and look at the code. Now use this as the model for creating the pick boxes. Use the worksheet_Change event to react to a selection for each pickbox and create the next one, positioning the user at that cell. Another way would be to create all the boxes and as choices are made, use the change event to delete the inappropriate ones based on the selection. Position the user at the next appropriate one. specific code requires specific knowledge. statements like "a drop-down pickbox" and "Question Two appears" and "and etc., etc. for seven questions. " are not statments that provide the requisite detail. -- Regards, Tom Ogilvy "RJB" wrote: I'd like to set up a spreadsheet whe - Question One is asked, and yes or no is selected from a drop-down pickbox. - If "Yes", Question Two appears (with a "Yes/No" box); if "No", Question Four appears (with a "Yes/No" box); - For Question Two, if "Yes", Question Four appears, and if "No", Question Three appears (with Yes/No boxes)... and etc., etc. for seven questions. Then, based on the answers, I want to display different solutions. There are twelve different outcomes. Thoughts? (I know how to do the pick boxes, but that's about it. I was thinking I could do the appearing bit with conditional formatting, but what do I know?) |
| All times are GMT +1. The time now is 05:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com