Excel – workflow / help desk flow chart

Hi,

I am looking to create an excel based help desk flow chart for level 1 support where clicking yes or no to a question will divert the agent to the next question, does anyone have an examples or ideas how to do this?
hellblazerukAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
What do you want?

DO you want a graphical representation or will a text based steps?

DO you want the Excel to move to the next question and highlight it?

mlmcc
0
hellblazerukAuthor Commented:
Hi,

DO you want a graphical representation or will a text based steps? - Either
DO you want the Excel to move to the next question and highlight it? - Yes

Alex
0
mlmccCommented:
Here is a minimal sample to give you an idea.

I added buttons for the answers and recorded a macro for each which was simply selecting the next question then stopping the recording.

mlmcc
Book1.xlsm
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Ejgil HedegaardCommented:
A little change to the workbook, giving more flexibility.
The principle is using Data validation, answer Yes or No.
A roadmap (on the sheet RoadMap) give the next question based on either Yes or No.
Then changing the roadmap change the workflow, without changing any code.
The roadmap sheet can be hidden.
Worksheet change event (column C) trigger the code to run.
The code is in the worksheet module.
Question-Answers-move.xlsm
0
Ken ButtersCommented:
Attached is a solution that uses a form, driven by a table.

The table can be constructed using a flowchart, and then the userform will follow the table in order to ask the next question.

A simplified example is attached.

In the Action (Yes) column, you would fill in the line number to go to next, if the user answers the current question as Yes.

In the Action (no) column you would fill in the line number to display next i fthe user answers the current question as no.

If you have a blank in either YES or NO column, then the buttons for Yes or no will not be visible.
HelpDesk.xlsm
0
Jeff DarlingDeveloper AnalystCommented:
The solutions provided so far are interesting.

Here are some other ideas to consider.  

How will you add new questions?  Will you just add entries to a sheet manually, or will there be some kind of dialog or form to assist?

Years ago, I found a web based "choose your own adventure" type game that is written in PHP.  The idea is that each page presents the users with two choices.  As the users go through the maze of questions, eventually they reach the end of the line.  There is either an ending solution, or not.   If there is no solution, then the users are encouraged to add one.

Here is a screen shot of the page at the end of the trail..

screenshot
0
Jeff DarlingDeveloper AnalystCommented:
0
hellblazerukAuthor Commented:
Thank you for the reply’s, some great example but the GUI interface from Ken is close to what I am looking to develop.

Ken,
I have already standard to add question to the table,
I have created a new screen before UserForm1 call Start where I plan to add 10 buttons to direct the user to the correct system before they answer questions
 Example buttons:  printer (step 10), phone (step 20), connection (step 30)

What code would I use under the button to update the current step and change the screen to UserForm1?
0
Ken ButtersCommented:
Variables in VBA have a certain scope.

For reference.... I'll call your new form "NewScreen", and the userform I created I'll call "UserForm1".

If I understand you correctly, when you open "NewScreen" and press a button you want "UserForm1" to be aware of what step to start at, with all steps being defined in the same existing table on Sheet1.

The problem is that UserForm1 is not open yet, so you can't really pass information directly to it from "NewScreen".

The way I would work around this is to create a variable at the top of module1 defined this way:

Public SelectedSystem as Long

Open in new window


I create an example of "NewScreen" userform and here is the code I put into it as an example:

Note... my starting line numbers are
"1" for system 1,
"10" for system 2,
"20" for system 3.

Private Sub cmdExit_Click()
   Unload Me
End Sub

Private Sub cmdSystem1_Click()
    Module1.SelectedSystem = 1
    Unload Me
End Sub

Private Sub cmdSystem2_Click()
    Module1.SelectedSystem = 10
    Unload Me
End Sub

Private Sub cmdSystem3_Click()
    Module1.SelectedSystem = 20
    Unload Me
End Sub

Open in new window


Now when you go to open Userform1, Userform1 will be able to access Module1.SelectedSystem to use as the starting step.

Then in Userform1 I changed the intialization routine from this:

Private Sub UserForm_Initialize()
    
    currentStep = 1
    Update_Question (currentStep)
    
End Sub

Open in new window


to this:
Private Sub UserForm_Initialize()
    
    currentStep = Module1.SelectedSystem
    Update_Question (currentStep)
    
End Sub

Open in new window


Here is new code in Module1 (it used to just Open UserForm1)
Public SelectedSystem As Long

Sub OpenForm()
    
    SelectedSystem = 0
    NewScreen.Show
    Unload NewScreen
    
    If SelectedSystem > 0 Then
        UserForm1.Show
    End If
        
End Sub

Open in new window


Now it will initialize SelectedSystem to zero.   If a button is selected in "NewScreen", then the value of selectedSystem will be updated to something Greater than zero.

If that is the case... it will then proceed to show userForm1, and the initialization routine in Userform1 will pick up the step to start at from "Module1.SelectedSystem" variable.

Attached is updated example with everything described above.

I added 3 systems.... and appended to each question (system #1) or (System #2) for example... so you could see that it was really using each system independently.
HelpDesk--1-.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ken ButtersCommented:
One additional note... the way it is coded right now... you cannot omit a step # in the step# column.... they have to stay sequential, and in order.

The reason for that is that it is treating the table like an array, and is using the action (yes) column values and the action (no) column values as indexes to the next row to display.  it is not searching the entire column of "Step #" for a matching value.  The code could be changed to search the column for a matching step #,  but depending on the size of your final table, it could potentially slow things down a little.

At any rate, the way it is coded right now, you could delete values from Step # column and it would still work the same, because it's not really using them.... they are there right now only for reference when you build the table.
0
hellblazerukAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.