Improve Dashboard UX! We’ll show you how to create contextual help using VBA! Then, we’ll show you how to improve the user experience. Build elegant screen tips step-by-step.
First, we will explain how to add useful contextual help to our spreadsheet. Then, how to boost user experience using an interactive help system?
You will find the answers to this question after reading the following tutorial. You are looking for a solution for another business issue that can interest many Excel users or managers. Take a look at our dashboard templates!
Differences between UX and UI
UI is a series of screen mockups, charts, and visual elements. These controls enable a user to interact with the spreadsheet components. Dashboard design is a crucial part of the UI. Typical user experience design elements are improving usability or enhancing customer satisfaction. End-user’s interaction with the spreadsheet or software is the key element of the user experience.
Create Contextual Help for Better Dashboard UX
Just a few words before we start. Let’s start with that unfortunate case when you work on a project for days, maybe weeks. You believe in rescuing the world, and in the end, something is wrong.
It’s nice and good, you might calmly relax, but there is such a significant volume of data on it that the outside user (your boss in most cases!) will not find his way on it. You can all agree that this is a very problematic situation.
Contextual help can dramatically improve the user experience of an interactive dashboard template. How can you help with this? As a solution, you will use a little programming. Don’t get discouraged by this! You will need only minimal knowledge.
We are using Office365 Pro Plus to create contextual help. The double-click-based writing into the shapes works fine.
Steps to build interactive screen tips in Excel
Step 1: On the ribbon, choose the Insert menu. Select the ‘Shapes’ option. Then, from the figures of callouts, insert one appropriate for you.
Step 2: Change the default style! First, select the inserted shape. Next, navigate to Drawing Tools on the Format tab. Finally, choose and add your preferred color scheme to the shape. If you want to apply a transparent background, don’t forget to remove the background color.
Step 3: And what about the explanations in the bubbles? We must assign the descriptions with the bubbles to enhance the user interface! It’s not necessary to insert an additional text box. Double-click on the bubble, and you can enter your description of contextual help.
Repeat this step four times. Now we have four shapes to place the correct information and explanations.
Step 4: We need to merge the inserted shapes into a single object. Hold the Ctrl button and select the objects. Right-click and select Group.
Step 5: On more essential things. You should add a name to the group. We do this by marking the group. Select the group, then click on the Name box field. You can find the Name box in the top-left corner. Finally, we name the group, in this case, ‘HelpBox’.
Pro Tip for handling the explanation: You have to do the following in the earlier versions of Excel. Select a chosen object. After pressing the ‘=’ key, select the cell containing the explanation that wants to be displayed. After pressing Enter, the content of the selected cell will appear in the bubble. The assigning is done!
Step 6: Insert a help icon! Go to the Insert tab (Illustrations Group). Choose your favorite icon. Click insert.
Improve dashboard UX using VBA
Step 7: Write and use this simple macro below:
Sub HelpSwitcher()
' Macro to Show & Hide Dashboard Help
ActiveSheet.Shapes.Range(Array("HelpBox")).Visible = Not ActiveSheet.Shapes.Range(Array("HelpBox")).Visible
[valHelpStatus] = ActiveSheet.Shapes.Range(Array("HelpBox")).Visible
End SubFor this reason, we have made a very simple little macro. The name of the macro is ‘HelpSwither’. The small code created the ‘HelpBox’ object in such a way. If you click on the question mark icon, calls show/hides the contextual help.
Click Alt+F11. Create a new module and paste the code above into the VBA Editor window.
Assign macro to the shape
Step 8: Finally, assign the macro to the inserted icon. The procedure’s last step is assigning the in / out macro switch to the question mark icon. The code is only a few lines, but it performs its task perfectly.
Now right-click on the icon! Select the ‘Assign Macro’ option. In the appearing window, choose the ‘HelpSwitcher’ macro and assign it to the icon with a click.
Check the final result in the picture below:
Conclusion
Contextual help in Excel is a simple but effective way to improve dashboard UX.