dtallia13

I am trying to create a process flow diagram. Upon opening the spreadsheet the user will see one question. There are only two answers to that question, yes and no, which are displayed as buttons. These were button 1 and button 2 which I renamed. These buttons are linked to different macros that bring up new questions and buttons.

Say the user clicks yes to the first question. This results in the first macro being triggered. A new question is now shown on the page along with two buttons for two new possible answer choices. These buttons are created and are given the automatic names button 3 and button 4. I can rename these buttons so that they are the actual answer choices.

If the user can click straight through the string of macros that I create, then they can reach the end of the questions that I ask and the necessary action can be shown on the screen. However, I want the user to be able to change a previous answer. Ideally, this procress 'tree' will have up to 10 questions on some branches and I don't want the user to have to restart with a mistake on question 9.

Ideally I want to rename each button so that it is the answer to a question(makes everything look neater and is visually easier to follow).
This is a problem when the user changes a previous answer as now the button can not be renamed. Here is a scenario.

Say the user presses "no" at first and the next question has three answer choices. This would prompt buttons 3, 4, and 5 to be created so that each choice has a new macro link. (Remember that the first question with yes and no answer choices creates buttons 1 and 2).

All of a sudden the user realizes that they meant to click yes for the first question. This prompts two new buttons to be created, but now they are buttons 6 and 7. Remember, if the user had correctly clicked yes to the first question without changing their mind, these buttons would be labeled 3 and 4. This is a big problem for renaming the buttons. When I recorded my macros they essentially say: create button #3 (automatic number here), select button #3, change name of button #3. Obviously if the user messed up and it is now button 6 this does not work.

I am essentially looking for a way to erase button numbering after the macro is triggered. This will make it so that any new buttons is labeled as 'button 1'which will quickly be rename to the answer choice I want. Therefore, when a user changes their mind and presses a new answer, the previous numbering will be erased, and a new label will be given. I know that there is a way to save and reopen the document, but I do not want the user to see this as it may confuse them (I am working with people that may have minimal excel experience). Is there any way around this or a better way to write my program.

Gruff

Sounds like you need to keep track of choices in some kind of undo structure. What you are getting into is separating your display routines from an internal logic engine.

Have you thought about writing this in VB6 or VB.NET where you can use the Standard TreeView control? It would do all the work for you and you can load and save the contents to a text or XML file.

I have some code around here in vb6 for a TreeView editor and a separate Viewer.

The editor allows someone to build the Tree graphically. Copy, Edit, and Prune branches. See Attached Pic.

The viewer allows users to drill down anywhere they like in the structure for content.

dtallia13

I'm glad you were able to follow what I posted! That is some good advice. I have no experience with VB6 and I am trying to keep this as basic as possible and in excel. I am now looking into just having a large spreadsheet with data validation. So if the first question is a yes/no answer then I want the user to be able to select yes/no in a drop down menu. If they select yes then I want all answer choices with no to be hidden.

Right now I have been trying to figure out how to write something with something like
If Target.Value = Yes Then
'Hide all Answer Choice No boxes'

No luck yet. Does anyone know how to do this. In the end I want the user to have answered enough questions to have one row with all of their previous answers and a solution.

Gruff

I do not think you appreciate the scope of what you are trying to acheive or how difficult it will be to do. What you are proposing is a brute force, massive set of hard coded decisions that branch to deeper and deeper nested decisions. This is made doubly difficult as you want both yes and no decisions.

This is absolutely the worst, time consuming and most rigid way to write what you want.
If you want to add a new question or branch you are going to have to know where in all these hard coded decisions you want to insert your new branch and all the logic it contains.

You can spend man years creating a hard coded system like this and more trying to alter or maintain it.

The problem is not whether to use Excel or some other programming tool. It's how your going about it.

dtallia13

Yes, I have begun to realize how difficult this is to do. The first way using buttons was very tedious and if any changes occured everythiing could be messed up if I wasn't careful.

Do you have any suggestions for the best way to do this. I currently have a spreadsheet with many questions, answers, and responses populated. That is in sheet1 and I essentially just need a way to pull information from it. In sheet3, which I was going to use to pull the information, I have the first question (cell A2), answer selection box (through data validation drop down menu in cell B2), and the next question to be asked (C2). The text in C2 is being pulled from sheet1 using vlookup based on the choice the user selects as the answer in B2.

I was thinking that I could use a Worksheet_change to activate a macro for the next question (Q2). Is there any way to use worksheet_change to say something like "if question 2, in cell C2 is "abcdefg" then run macro. Right now I can only run the macro if the cell itself is populated, which does not account for the different quetions that need to be prompted. I want one specific macro to run if the cell is populated with "abcdefg". The top command works, the bottom one is more specifically what I want.

If Target.Address = "$B$2" Then Application.Run _
"ProcessSpreadsheetmacroattempt.xlsm!Q2_Whatkindofchangeisrequirre d"


If "$B$2" = No Then Application.Run _
"ProcessSpreadsheetmacroattempt.xlsm!Q2_Whatkindofchangeisrequirre d"

If I can just figure out how to prompt question 2 (and therefore all following questions using the same commands) then I can finish the spreadsheet (through a long and tedious process involving multiple macros).

Thanks for the help so far, I have very little starting knowledge using VBA/ other programs so this has been a huge learning experience.