REAL-TIME

VBA Projects

Full Access with Source Code

  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

120+ PROFESSIONAL

Project Management Templates

120+ PM Templates Includes:

  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

Share Post

This tutorial explains creating interactive dashboards using Excel VBA with example file which you can download. We will see the basic things that we need to understand to create dashboards and then we will learn how to create stunning and rich visualized dashboards.

We will develop a simple interactive dashboard, I will take the last updated simple sample dashboard and enhanced it by using VBA.

In this tutorial:

Creating interactive dashboards using Excel VBA

What is a Dashboard? And what are the Interactive Dashboards?

Dashboard is a kind of report or a simulation tool to displaying important data or information at a single place to get the quick understanding of the data. Dashboards are visually displayed with combination of Tables, Charts and Pivot tables and Insights for Analyzing the Data. Dashboards helps to quickly understand the key metrics and delivered as simulation tool for quick data analysis.

When we deal with large amount of data (time series/ historical) or data with more number of categories, Fields/Columns/Variable or Records, we need to provide interactivity to understand the data in-depth. For example, you may be developing a dashboard to understand the sales of a Super Market. Assume, you have the sales data for last 5 years, and it has 5000 stores across the world. You can’t understand the each by developing a simple dashboard with this large amount of data. You have to provide different options to choose different categories of the Super Market. For example, if you want to understand the data based on the demographic variables, you should provide the user interface to select Countries, Regions, States, Income Levels, Age Groups and Gender etc…

Concepts we should understand to develop Interactive Dashboard:

We can develop the interactive dashboards using Excel. We should know the following concepts to build Interactive Dashboards:

Form Controls:

We can use Form Controls available in the Excel to provide the user interface and link it to an object.
Interactive Dashboards- form controls

Charts and Sparklines:

Charts will help you to provide the graphs for the required metric to quickly understand the respective data. You can make them interactive by using VBA or linking the From Controls with Charts.
dashboards - charts and sparklines-pivot tables

Tables, Pivot Tables and Slicers:

Tables and Pivot Tables help you to present the data in table format, Pivot tables help you to summarize the data. We can use slices and VBA to develop interactive pivots.
dashboards-tables pivot tables

VBA

You can develop the rich visualized dashboards using VBA. as discussed above it helps make the charts and table more interactive. And also helps to filter the data or change the data source. You can get the external data using VBA and provide many mare advanced and interactive features.

excel vba programming - create interactive dashboards

Practical Learning – Creating Dashboards using Excel VBA

You can download the example file

[Sample Interactive Dashboard (Sales Data).xlsm] and have a look into this.

Steps for Creating interactive dashboards using Excel VBA

  • Design the dashboard : First you have to prepare a rough design of you dashboard based on your customer requirement. And it should approved by your customer before you start developing the dashboard
  • Plan: Once you are clear with your requirement and design, you have to plan how to achieve it. you can write simple steps to develop the dashboard
  • Develop: Start developing your plan and use the VBA if required. Generally developers use VBA to provide dynamic feature to the user
  • Test and deliver: Check if all the features are working correctly or not and fix if there are any bugs. You can provide a test copy to user for testing, and rquest user feedback. Finally you can deliver once you are done with the changes.
How It’s become interactive?

I have used the last updated simple sample dashboard and done the following changes.

» Saved the sample file as macro enable workbook (i.e; .xlsm format): you must save the file as macro enabled, otherwise it will not save your VBA code.
» Added two ComboBoxes in Dashboard Worksheet and name them as cmbRegion and cmbCategory. The first one (cmbRegion ) is for providing the drop-down list to the user to select regions. And the second one (cmbCategory) is for providing the drop-down list to the user to select Categories.

Creating interactive dashboards using Excel VBA - drop downs

» Added the following code for ThisWorkbook Moduel for Workbook_Open event: It will populate the drop-down list with required list item while user opens the file every time.

Creating interactive dashboards using Excel VBA - populate list

» Add the Following code in Dashboard Module for ComboBox_Change Events : We are capturing the user selected Category and Region.

Creating interactive dashboards using Excel VBA - events

» You are done! This is a simple dashboard using VBA, we will see creating complex dashboards later.

Tools for Developing Rich Interactive Dashboard

Excel is the most widely used tools for developing regular dashboards. If you have large amount of data and you have many tools to develop rich visualized tools. The following are the most widely used Dashboard development and BI Tools. Qlikview , TIBCO Spotfire and Tableau are rich visualized BI Tools. They are quick and can deal with large amount of data.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates

Related Posts

Creating Dashboards in Excel – Dashboard Creation Using Sales Data
Creating Dashboards in Excel – Dashboard Creation Using Sales Data
Gallery

Creating Dashboards in Excel – Dashboard Creation Using Sales Data

April 8, 2013|10 Comments
Categories: Excel DashboardsTags: Last Updated: June 17, 2022

34 Comments

  1. Asha June 22, 2015 at 8:24 PM - Reply

    I would like training in excel dashboard. Do you offer this? Thanks. Asha

  2. PNRao June 23, 2015 at 11:06 PM - Reply

    Hi Asha,
    Yes, we are going to start our online classes in couple of weeks. We will inform you before we launch our program.

    Thanks-PNRao!

  3. Trish July 20, 2015 at 5:25 AM - Reply

    Hi! Please notify me as well.
    It would be very appreciated.
    Thank you so very much. Regards,

  4. PNRao July 20, 2015 at 1:24 PM - Reply

    Hi Trish,

    Thanks for your interest to learn VBA. Sure, we will inform once we are ready.

    Thanks-PNRao!

  5. Darren September 9, 2015 at 12:46 AM - Reply

    Hi, are you ready to offer training?
    Thanks.

  6. PNRao September 9, 2015 at 1:07 AM - Reply

    Hi Darren,

    We are creating VBA online classes (Launching on 1st Oct 2015) and then we are going to prepare online course for ‘Developing Dynamic Dashboards using MS Excel’ (Planning to launch on 1st November 2015).

    Please let us know if you are interested in any of the above courses, so that we can inform you when we launch our classes.

    Thanks-PNRao!

  7. Darren September 17, 2015 at 1:21 AM - Reply

    Yes, I am interested in both the VBA and Dynamic Dashboard courses.
    Do I need to learn any basics as a pre-requisite?

  8. PNRao September 17, 2015 at 12:25 PM - Reply

    Hi Darren, The pre-requisite is MS Excel, it is good to have basic understanding of Excel and its utilities. However, we will cover basics of Excel, before going to the actual topic.

    Thanks-PNRao!

  9. Moataz October 1, 2015 at 8:10 AM - Reply

    I’m interested in VBA online classes and Developing Dynamic Dashboards using MS Excel online course need more details.
    Thanks,
    Moataz

  10. PNRao October 2, 2015 at 1:19 PM - Reply

    Hi Moataz,

    We are creating VBA online classes (Launching at the earliest) and then we are going to prepare online course for ‘Developing Dynamic Dashboards using MS Excel.

    We will inform you when we launch our classes.

    Thanks-PNRao!

  11. Mark October 2, 2015 at 1:51 PM - Reply

    I am interested as well. Please let me know when.

    Thanks!

  12. PNRao October 2, 2015 at 2:31 PM - Reply

    Thanks for showing interest on VBA Classes. We are working on it. It will be available soon. We will notify you once we launch our classes.

    Thanks-PNRao!

  13. Manjunath Channappa October 12, 2015 at 6:27 PM - Reply

    count me in as well pls…

  14. Manjit October 15, 2015 at 11:35 AM - Reply

    Please let me know i am interested in learning VBA dynamic dashboard.

    Regards

  15. Ruquaiya October 26, 2015 at 12:44 PM - Reply

    I’m interested in VBA online classes and Developing Dynamic Dashboards using MS Excel online course need more details.
    Thanks,
    Ruquaiya

  16. PNRao November 3, 2015 at 4:11 PM - Reply

    Thanks for showing interest on VBA Online Classes and Dashboards. We are working on it. It will be available soon. We will notify you once we launch our classes.

    Thanks-PNRao!

  17. Dhiraj November 4, 2015 at 12:32 PM - Reply

    Dear Sir,

    First of all thanks for sharing such a valuable information …

    I want to be part of your online programmes so please let me know detailed information..

    Wishing you a Very Happy Dipawali in advance..

    Have a great day..

    Dhiraj

  18. PNRao November 7, 2015 at 11:24 AM - Reply

    Sure, we will inform you while launching our online courses.
    Thanks-PNRao!

  19. Julie November 9, 2015 at 6:35 PM - Reply

    I am also interested in VBA and dynamic dashboards.

    Thanks and best regards,
    Julie

  20. AR November 15, 2015 at 9:35 PM - Reply

    When is the next course on dynamic dashboards using excel.

  21. PNRao November 16, 2015 at 4:03 PM - Reply

    Hi All,
    Thanks for showing interest on our online course, we are working hard to launch this program as soon as possible, we are planning to launch on Jan 1st 2016.

    Thanks-PNRao!

  22. PNRao November 16, 2015 at 4:11 PM - Reply

    Hi, Thanks for showing interest on our online courses. We are working on the program, will inform you when we launch the course.
    Thanks-PNRao!

  23. Max November 20, 2015 at 4:53 PM - Reply

    Hi
    I am also interested in VBA and dynamic dashboards.

    Thanks and best regards,

    Max

  24. harjeet singh November 30, 2015 at 8:08 PM - Reply

    Hi, I am currently working as a engineer ia company that is manufacturing based .The machine that i undertake in operation have lots of calculation like current and voltage to calculate power heat input mass and many more so help me how i can make userform,

  25. harjeet singh November 30, 2015 at 8:11 PM - Reply

    sir i also want to learn my contact is plz inform me

  26. Rich December 2, 2015 at 5:08 PM - Reply

    Hi
    I am also interested in VBA and dynamic dashboards.

    Thanks and best regards,

    Rich

  27. PNRao December 14, 2015 at 7:45 AM - Reply

    Hi Singh,

    Here is the sample examples to create userform. Its explained with examples.

    Link 1: http://analysistabs.com/vba-code/excel-projects/data-entry-userform/

    Link 2: http://analysistabs.com/vba-code/excel-projects/calculator-userform/

    Regards-Somu

  28. Jeya February 18, 2016 at 9:39 PM - Reply

    I like to learn dashboard through dvd.pls suggest me any DVD available in market

  29. Zahida March 30, 2016 at 2:16 PM - Reply

    Hi
    I am also interested in VBA and dynamic dashboards.

    Thanks.
    Best regards,
    Zak

  30. Varsha July 12, 2016 at 12:08 AM - Reply

    Hi,

    I tried to execute this code, till drop-down part I’m able to get output but further that I am not able to link my charts.
    Please help me as early as possible

  31. prachi August 25, 2016 at 11:38 AM - Reply

    I am also wiling to learn dashboard creation, can you arrange the same again?

  32. Udit Kapoor July 5, 2017 at 5:33 PM - Reply

    Hello!

    I have my MBA in Business Analytics. I’m interested in learning VBA Dashboards using VBA.
    Kindly assist me for the same.

    Thanks!
    Udit Kapoor

  33. SHESADEV SHA August 3, 2017 at 4:07 PM - Reply

    HI,
    I want to learned excel VBA DASHBOARD AND courses through online.

    Please intimate me when your batch should be start .

    Thanks
    Shesadev

  34. K.UmaRajesh October 13, 2017 at 4:08 PM - Reply

    Hi Sir, I am Rajesh, I work mostly with Excel and I want to expertise in Excel. Please help!

Leave A Comment