Enhance Dashboard UI – How to boost user experience using interactive excel VBA help system? How to enhance dashboard visualization? You will find the answers for these questions after reading today’s article. We are looking for a solution for another business issue that can interest many Excel users, dashboard designers or managers.

Let’s start with that unfortunate case when we work on an Excel dashboard for days, maybe weeks, we believe to rescue the world and at the end there is something just not right.

It’s nice and good, we might calmly relax, but there is such a great volume of data on it that the outside observer (who is our boss in most cases!) will not find his way on it.

We can all agree that this is a very problematic situation. How can we help this?

As a solution we will use a little VBA programming, but don’t get discouraged from this you will need only minimal knowledge. Enhance Dashboard UI using VBA!


Enhance Dashboard UI – Help system

For you to see how helpful tool we have made here is a little animation in advance.

By clicking on the blue question mark the dashboard geared explanation will appear and at the next click it will disappear.

And now let’s see the detailed explanation how can you make a similar dashboard.

The first task is to create a new sheet on the worksheet.

After this we fill up information into as many cells as many bubbles we have to portray.

In our example we will use four information panels that will be placed on critical parts of the dashboard.

From the Insert menu chose the ‘Shapes’ option and then from the figures of ‘callouts’ insert one that is appropriate for us.

Repeat this for four times, with this we created the bubbles in which we will place the correct information and explanations.

enhance dashboard ui shape insert

Recolor and add some shade to them so they will be spectacular.

We would like to manage the four shape objects together therefore we have to merge them.

This is how we do it: mark out the four objects together than right click and the appearing menu chose ‘Group’ option.

We have accomplished that Excel controls the four objects together from now on.

enhance dashboard ui merge

On more very important thing is to name the objects.

We do this by marking the group containing the four elements and then click in the ‘Name box’ field we name the group, in this case this will be ‘HelpBox’.

enhance dashboard ui namebox

And we are all done, we will control this object with a help of a switch. How can we do this by the help of Visual Basic for Application? This will be the subject in the last chapter.

And what about the explanations in the bubbles? We must assign the explanations with the bubbles to enhance dashboard ui!

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!

Enhance Dashboard UI – VBA Help Switcher

As you can see on the picture for this reason we have made a very simple little macro, named ‘HelpSwither’. This operates the previously created ‘HelpBox’ object such way, that if you click on the question mark icon calls forth / hides the text instructions.

Enhance Dashboard UI macro

The program is only a few lines but it carries out its task perfectly. The last step of the procedure is to assign the in / out macro switch to the question mark icon.

This is as follows: with the use of the right click display the properties of the icon (which is a picture) than chose the ‘Assign Macro’ option.

In the appearing window chose the HelpSwitcher() macro and with a click assign it to the icon. Save the workbook and the procedure is done.

Hopefully you liked today’s Excel dashboard UI presentation!

Today we have shown you step by step what methods you can use in the design of a dashboard user interface to make a report unique.

The template is downloadable here, and soon our series introducing dashboards continues!