Solved

Excel – workflow / help desk flow chart

Posted on 2014-07-29
11
454 Views
Last Modified: 2014-08-01
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?
0
Comment
Question by:hellblazeruk
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 40228948
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
 

Author Comment

by:hellblazeruk
ID: 40229044
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40229439
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
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 40229674
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
 
LVL 19

Expert Comment

by:Ken Butters
ID: 40229756
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40231616
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
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40231621
0
 

Author Comment

by:hellblazeruk
ID: 40232583
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
 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 40232765
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
 
LVL 19

Expert Comment

by:Ken Butters
ID: 40232785
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
 

Author Closing Comment

by:hellblazeruk
ID: 40233562
Thank you
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now