USER FRIENDLY EXCEL VBA ENABLED COLOURED BUTTONS

Today's hack is about my below last VBA enabled Excel Dashboard type data presentation.
If you haven't gone through that Dashboard, please follow the below link

https://dailypro123.blogspot.com/2020/03/a-quick-vba-enabled-excel-data.html

To make it easier to deliver in parts, i have divided the whole dashboard into below parts

1-User friendly Excel Coloured Button
2-Automatic Sorting Tool
3-Excel Scroll Bar to present data in dashboards
4-Dynamic Charting and automatic filtration of data (without the use of filter commands in excel-by using formulas)

We will go through each part in next coming posts.

This post shall cover the first topic - USER FRIENDLY EXCEL COLOURED BUTTONS

Database

The data is about WHO report of World health status - Mortality figures. It is available to us in the below format for 192 countries.

We have changed the shape of this random data into a single screen dashboard.

STEPS TO CREATE BUTTONS

Data available to us is given country and WHO region wise. WHO has categorized countries into 6 different sections.

We will have one button for each of the section. Upon clicking that button, data related to those countries shall appear in the dashboard.

One thing to mention here - although the work which this button shall perform can also be done by using the Pivot Table Slicers. I'm not using the slicer here as buttons can perform multiple task - this would depend upon the coding and formulas we use on buttons while slicer is ready-made tool with limited graphical options and that work with pivot tables only.

STEP NO.1

Select the range of cells and add the colour to them. I have added blue colour with white Borders. This is done as shown in the below picture.

STEP NO.2

Go to Insert tabe > Shapes and draw a rectangular shape.
Place this shape on top of any button.
Change the colour of the shape from Fill to No-Fill. Now you can see the back layer of excel. Do the same with the Border colour of the Shape.
Copy this shape 7 times and place on top of each button.

STEP NO.3

Assign each button catagory a numeric number. I have assigned below numbers to each catagory. Next part of this post will explain why i did so.

Button category	Assigned numeric number
World	1
AFR	2
AMR	3
SEAR	4
EUR	5
EMR	6
WPR	7

We write VBA code now which shall return a numeric number once clicked i.e. if we click AFR button then VBA code shall return numeric number "2" to a designated spreadsheet cell.

STEP NO.4

Go to developer tab > View Code.
Write one module for each button i.e. seven modules.
Below is snapshot of the "World" button module (VBA Code which i have written.

I have given output value of the code as "1" so you would have get now that this is done for the "World" Button.

Copy past the same code to all other 6 modules and change their respective output values.


STEP NO.5

Having written all 7 macros/modules. Now just right click on each shape which was placed on top of buttons (with no fill features).

Right click > Assign Macro

Do the same for all 7 shapes and assign them their respective macros.
Now once you will click any button, its defined output value shall appear in the defined range in code.

STEP NO.6

Button shall be doing its work now. But not changing the colour. To change the button colour we will do the conditional formatting. We can also do the same with VBA coding but i would prefer the conditional formatting to keep the VBA code at minimum.

Just select the cell where we placed the button shape and write below conditional formating formula in conditional formating bar.

In the formating tab - I have selected the orange colour.

So now once the shape button is clicked, VBA code shall return the output value and conditional formatting tool shall change the colour of the button to give it exact button like tool.

Your buttons shall look like below

Please let us know with your feedback in the comments section. Your feedback shall help us to keep improving our posts.
Also please let us know if we you have any query  - we would be happy to assist.

Thanks..........Stay tuned for the below next parts of the dashboards..........

2-Automatic Sorting Tool
3-Excel Scroll Bar to present data in dashboards
4-Dynamic Charting and automatic filtration of data (without the use of filter commands in excel-by using formulas)

Follow and subscribe our blog to get notification of all updated in future.

Learn Continually - There is always "One more thing" to learn. Steve Jobs