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

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

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

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

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.

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