Create contextual help for your dashboard using VBA! We’ll show you how to improve user experience. Build elegant screen tips step-by-step.
First, we will explain how to add a useful contextual help to our spreadsheet. How to boost user experience using interactive help system?
You will find the answers for 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.
Differences between UX and UI
UI is the series of screen mockups, charts and visual elements. These controls enable a user to interact with the spreadsheet components. Design is extremely important part of the UI. UX provide seamless interaction between the user and the product. Typical user experience design elements are improving usability or enhancing customer satisfaction. End-user’s interaction with the spreadsheet or software is the key elements of user experience.
Create Contextual Help – Step by step Tutorial
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 to rescue the world and at the end there is something just not right.
It’s nice and good, you might calmly relax, but there is such a great volume of data on it that the outside user (who is 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 interactive excel dashboard.
How can you help this? As a solution you will use a little programming. Don’t get discouraged from this! You will need only minimal knowledge.
Steps to build interactive screen tips in Excel
Step 1: On the ribbon choose the Insert menu. Select the ‘Shapes’ option. From the figures of callouts insert one that is appropriate for you.
Step 2: Change the default style! First select the inserted shape. Navigate to Drawing Tools on the Format tab. Select and add your preferred color scheme to shape. If you want to apply transparent background don’t forget to remove the background color too.
Step 3: And what about the explanations in the bubbles? We must assign the explanations with the bubbles to enhance user interface! It’s not necessary to insert additional text box. Just double click on the bubble and you can enter your description of contextual help.
Repeat this step for four times. Now we have four shapes in which we will 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 important thing. You should add a name to the group. We do this by marking the group. Select the group then click in the Name box field. You can find the Name box on the top-left corner. Finally, we name the group, in this case ‘HelpBox’.
Pro Tip for handling the explanation: In the earlier versions of Excel, you have to do the following. Select a chosen object then after pressing the ‘=’ key select the cell containing the explanation wanted to be displayed. After pressing Enter the content of the selected cell will appear in the bubble. The assigning is done!
We are using Office365 Pro Plus to create contextual help. The double-click based writing into the shapes works fine.
Step 6: Insert a help icon! Go to Insert tab (Illustrations Group). Choose your favorite icon. Click insert.
Use small VBA macro to start the engine
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 Sub
For this reason, we have made a very simple little macro. The name of the macro is ‘HelpSwither’. The small code created ‘HelpBox’ object such way. If you click on the question mark icon calls show / hides the contextual help.
Click Alt+F11. On the VBA Editor window create a new module and paste the code above.
Assign macro to the shape
Step 8: Finally assign the macro to the inserted icon. The last step of the procedure is to assign the in / out macro switch to the question mark icon. The code is only a few lines, but it carries out its task perfectly.
Now right click on the icon! Select the ‘Assign Macro’ option. In the appearing window chose the ‘HelpSwitcher’ macro and with a click assign it to the icon.
Check the final result on the picture below:
Ways to improve UX for your dashboard
Creating contextual help is not the only solution to improve user experience in Excel.
Here is our additional list if you are using large data tables.
- Freeze Top Row: Keep the first row of the table visible while you scroll down! Use this feature if you want to keep important information always on the top. For example, table headers with column names.
- Enable Horizontal Scroll: Use this function if you have large data sets and you have important data in the first column. Go to the View Tab and choose ‘Freeze first column’.
- Apply row styles: Frequently we lose the focus when browsing large tables. Use alternate formatting (table styles) to keep our eyes on the main content.
- Add mouse over tips: Use HYPERLINK function in Excel to create nice info-boxes for the details
- Use the GROUP and UNGROUP function to drill-down into details.
Contextual help in Excel is a simple but effective way to improve UX.