+ Reply to Thread
Results 1 to 39 of 39

Interactive Charts/Dashboards

  1. #1
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Interactive Charts/Dashboards

    I was very impressed by the winner of the dashboard competition.

    http://www.exceltip.com/finance/mult...bryant414.html

    Can someone tell me how to make these charts that are interactive? Based on how you select a piece of the pie chart, for example, it moves the piece of the pie and filters the other tables and charts on the dashboard. I've been searching and wasn't able to figure this out.

    Thanks!

  2. #2
    Registered User
    Join Date
    02-14-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Interactive Charts/Dashboards

    Thanks for your feedback : )

    As for the charts, if you open the dashboard and hit Alt-F11 to bring up the VBA window, you should see the folder for Class Modules. Expand that and open clsChartInteraction.

    I've commented this out pretty thoroughly in the workbook itself. I'd like to note that I certainly didn't figure this out on my own, either: there's a link in the comments to one of the threads (here on ExcelForum) that I used to figure out what I was doing.

    From my comments in the dashboard:

    Please Login or Register  to view this content.
    I'd recommend reading through my notes and experimenting with a single chart first. In the Module modDashboard, the three macros at the top are essential: first declaring the class module, and setting a sub for InitializeCharts (makes them interactive), and ResetCharts (turns off interaction, so you can edit the charts).

  3. #3
    Registered User
    Join Date
    10-01-2014
    Location
    Melbourne,Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: Interactive Charts/Dashboards

    Hi Kbryan,
    I start to read your code, but there is a lot of things i don't understand as my vba is limited. Would you please advice how to find some information about the syntax
    EX: I try to see how do you layout your background color and size for dashboard, but I could not find any coding for that.
    I see this bit of code but do not understand what does it do?
    [ActiveSheet.Shapes.Range(Array("shpRAW")).Select
    Selection.ShapeRange.ShapeStyle = msoShapeStylePreset27]

    Or

    [ActiveSheet.Shapes.Range(Array("shpRAW")).Select
    Selection.ShapeRange.ShapeStyle = msoShapeStylePreset6
    With Selection.ShapeRange.Fill
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorLight1
    .ForeColor.TintAndShade = 0
    .Transparency = 1
    .Solid
    End With]

    Also I like to know that you layout the dashboard and creating the charts first before writing code or what are the steps should we follow?
    Last edited by mhghg; 12-10-2014 at 06:27 PM. Reason: extra information

  4. #4
    Registered User
    Join Date
    02-14-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Interactive Charts/Dashboards

    Go to the Class Module, clsChartInteraction, and search for chDashSelect. This has the code which changes the color of the navigation bar (column A).

    The macros actually do not change any of the other formatting. Each dashboard is attached to a different range of columns (each with its own colors). The macro hides all the dashboards, then unhides only the dashboard we want to see.

    The code you are looking at is changing the buttons on the navigation pane. The circles and triangle. All it is doing is changing them between one preset, where the shape is filled in, and another preset, where the shape is transparent. This helps users identify whether each button is toggled on or off.

    In regard to order... I would definitely recommend that you first get your data situated in an easy-to-reference format (I prefer tables). Next, build out the dashboard layout. After that, create isolated sets of data that you will use to create your charts (you can see these by unhiding rows 51 and below on the Dashboard tab). Create your charts, arrange them in a format that is easy on the eyes but provides all of the relevant data.

    After all of this is done, you would dive into the VBA to make the charts interactive. Also, I cannot stress this enough: save your work before you tweak the VBA, because it can, and will, mess up your layout and formats if something isn't written quite correctly.

  5. #5
    Registered User
    Join Date
    10-01-2014
    Location
    Melbourne,Australia
    MS-Off Ver
    2010
    Posts
    21

    Lightbulb Re: Interactive Charts/Dashboards

    Thank you for your fast response. I pull my hair for the whole morning trying to understand your class module. But I end up having a headache. Can you show me how to unhide the row 51 and columm B to AA?
    Last edited by mhghg; 12-11-2014 at 05:42 AM. Reason: SOLVED

  6. #6
    Registered User
    Join Date
    10-01-2014
    Location
    Melbourne,Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: Interactive Charts/Dashboards

    Do you use the pivot table to create the charts?

  7. #7
    Registered User
    Join Date
    09-10-2013
    Location
    indonesian
    MS-Off Ver
    O365
    Posts
    44

    Re: Interactive Charts/Dashboards

    any one found, how to create interactive chart, like kbryant have

  8. #8
    Registered User
    Join Date
    10-01-2014
    Location
    Melbourne,Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: Interactive Charts/Dashboards

    Read his explanation from the top for me. That is where you should start if you are having enough knowledge of VBA. I don't so I need to start from very beginning.

  9. #9
    Registered User
    Join Date
    09-10-2013
    Location
    indonesian
    MS-Off Ver
    O365
    Posts
    44

    Re: Interactive Charts/Dashboards

    yuuppppss, I need to start from very-very beginning too, I interested with Kbryant done especially with his chart menus, when we click it, it will changed display...it's awesome

  10. #10
    Registered User
    Join Date
    10-01-2014
    Location
    Melbourne,Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: Interactive Charts/Dashboards

    Hi Kbryant414,
    I wonder if your data source can be updated automatically and all the table calculations will reflect the changes?
    Say if we get the data from sql server.

  11. #11
    Registered User
    Join Date
    10-01-2014
    Location
    Melbourne,Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: Interactive Charts/Dashboards

    To Fandi_Damore: Perhaps we can work together to figure it out if you like.

  12. #12
    Registered User
    Join Date
    09-10-2013
    Location
    indonesian
    MS-Off Ver
    O365
    Posts
    44

    Re: Interactive Charts/Dashboards

    I think that good idea mate...
    when we can start

    dashboard - kbryant.PNG

  13. #13
    Registered User
    Join Date
    02-14-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Interactive Charts/Dashboards

    To unhide rows and columns, first you need to go to the Review tab on the ribbon, and click Unprotect Sheet (there is no password). Then, highlight row 50 and drag down, right-click the highlighted area, and choose to Unhide. You can do the same with columns, but note that the macro to change between dashboards will hide columns again.

    As it currently is, it does not automatically update. However, it would not be difficult to get it to do so. All of the tables and charts are built off of the data in its original format, meaning you could easily link to an external data-source or drop in data from the SQL.

    Unfortunately, while I can answer specific questions, I can't do a walkthrough from step one. One thing to get you started, though: my macros/VBA actually have very little to do with making the charts and data change.

    It's a little lengthy, but when you unhide the rows, you'll see most of the formulas in the hidden area look like this one:

    Please Login or Register  to view this content.
    The part in orange is simply to pull the data for the item on that particular line, from the full data tables. The part in red, meanwhile, is what makes the charts interactive and dynamic.

    Each dashboard has one of these hidden below it:

    chInteract.bmp

    The red part of the formula references what's in this box. If it's an asterisk (for text values) or ">0" (for numeric values), it's considered a wildcard, and includes all data. However, when the user clicks Product1 on the chart (or selects it from the dropdown menus), the macro changes that box to say Product1. Then, all the numbers update to show Product1 because the red part of the formula requires it.

    Having the data change when you click the chart is a novelty. The important part is building the formulas to show only the information that has been selected.

  14. #14
    Registered User
    Join Date
    10-01-2014
    Location
    Melbourne,Australia
    MS-Off Ver
    2010
    Posts
    21

    Re: Interactive Charts/Dashboards

    Hi Fandi,
    As we are approaching Xmas time so I don't actually want to sit down to study now, and I also go for holiday one month so I will not work with you as i wish.When I come back I will contact you
    see where you are up to. I think kbryant said that just start with one first so I think i will start with Financial board first.

  15. #15
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    Has anyone had time to play with this? I'd like to see a VERY basic example.

  16. #16
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    I think this details out how this all works...

    http://peltiertech.com/category/vba/

  17. #17
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    I think this details out how this all works...

    http://peltiertech.com/category/vba/

  18. #18
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    Question... In kbryant's spreadhseet, how is "chFinRegionPie" defined?

  19. #19
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    Is it just in the name manager? I didn't see it listed when I pulled that up.

  20. #20
    Registered User
    Join Date
    02-14-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Interactive Charts/Dashboards

    It's the name of one of the charts. Rather than keeping the default "Chart1", when building reports, I consider it good practice to use consistent naming schemes.

    Types (prefix):
    t = Table
    ch = Chart
    dd= Dropdown (I neglected to use this prefix in the dashboard)
    Data Set:
    fin = Financial
    sls = Sales
    prf = Performance
    hr = Human Resources
    stock = Stocks (should have shortened this more)
    So on and so forth.

    chFinRegionPie refers to the Chart on the Financial Dashboard which displays Regional data in a Pie.

    On the menu bar on the left, if you click the Triangle shape, you can turn charts non-interactive. This allows you to select them in order to check or change their names, or adjust their data.

  21. #21
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    Thanks, I did notice that once I made the chart interactive I was able to see the name.

    I'm reading your notes over and over and understand some of it at a high level. I'm trying to break it into pieces so I can understand what's needed to create a very simple example of the exploding interactive pie chart.

    Thank you for documenting your code so well. It helps to understand what's going on.

  22. #22
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    I was able to get the exploding pie chart to work. Pretty cool. Is there something special you need to do to get it to work as a part of an embedded worksheet and not it's own chart sheet?

  23. #23
    Registered User
    Join Date
    09-10-2013
    Location
    indonesian
    MS-Off Ver
    O365
    Posts
    44

    Re: Interactive Charts/Dashboards

    could you share the file?

  24. #24
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    Sure. I had to recreate the working version I had. The Chart sheet works. It will explode and unexplode the chart. I've been reading Peltier's write up here -

    http://peltiertech.com/chart-events-microsoft-excel/

    It explains it and provides an example, but I don't think I understand all of what's needed and why it's needed.


    Book1.xlsm

  25. #25
    Registered User
    Join Date
    09-10-2013
    Location
    indonesian
    MS-Off Ver
    O365
    Posts
    44

    Re: Interactive Charts/Dashboards

    thanks a lot dspblues, you make it more simple to understand

  26. #26
    Registered User
    Join Date
    11-01-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Interactive Charts/Dashboards

    This is precisely the thing I was looking at, but can't quite get it to work.

    Please Login or Register  to view this content.
    But All I get for an answer is "0". Am I missing something in the structure of the function?

    I think the filtered tables part is pretty awesome so any idea as to what I would need to tweak would be greatly appreciated.

  27. #27
    Registered User
    Join Date
    02-14-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Interactive Charts/Dashboards

    The fact that you get a 0 instead of #N/A means that the formula is put together correctly, but it's not finding all the values from @Name or B1:B4.

    If you're trying to use wildcards, remember that * is for text values and >0 is for numeric values. If not, make sure the values in @Name and B1:B4 are in the columns being searched.

    Can't determine much more without seeing your spreadsheet.

  28. #28
    Registered User
    Join Date
    11-01-2013
    Location
    Connecticut
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Interactive Charts/Dashboards

    Thank you! Your answer fixed it! I was using * in a cell where I was looking for a number.

  29. #29
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    Any help would be appreciated. I'm trying to get the pie chart explosion to work on a chart that's on a worksheet.
    Attached Files Attached Files

  30. #30
    Registered User
    Join Date
    02-14-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Interactive Charts/Dashboards

    You need to declare variables at the top, and open a "With" statement.

    Also, you'll want to add .Chart after .ChartObjects("chPie") -- it sounds redundant but VBA requires it.

    Please Login or Register  to view this content.

  31. #31
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    Thanks. Complete newb here. New to programming. Reading John Walkenback's 2013 Power Programming with VBA book now.

  32. #32
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    Can someone give me a hand with this "else without if" compile error? I can't figure it out. I was working on adding multiple chart support to my CChartEvent Class.

    I'm sure this is something simple.

    ChartClassEvents - Filtering Charts.xlsm

  33. #33
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    Fixed it... Turns out I didn't need to used ElseIf, I think.

  34. #34
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    Here's an update on where I am. The "interactive chart" toggle isn't working right, the reset part is throwing an error I need to figure out.

    Also, sometimes the charts get a little screwy the more you explode and play them, but it's coming along.

    Good thing is I think I understand a fair amount of what's going on.


    ChartClassEvents - Filtering Charts.xlsm

  35. #35
    Registered User
    Join Date
    09-10-2013
    Location
    indonesian
    MS-Off Ver
    O365
    Posts
    44

    Re: Interactive Charts/Dashboards

    hello dspblues, I think you forget to add letter "s" in sub reset chart, for clsChartevents,

    I have error message when upload the file

  36. #36
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Cool Re: Interactive Charts/Dashboards

    Here's my latest. I had fixed that error and made another couple improvements. I hope this helps others.

    ChartClassEvents - Filtering Charts v2.xlsm

  37. #37
    Registered User
    Join Date
    09-10-2013
    Location
    indonesian
    MS-Off Ver
    O365
    Posts
    44

    Re: Interactive Charts/Dashboards

    hi dspblues,

    I still have confuse to make this one, what about you ?
    Attached Images Attached Images

  38. #38
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    Re: Interactive Charts/Dashboards

    What part of this are you working on? I did get the interactive chart piece to work.

  39. #39
    Registered User
    Join Date
    09-10-2013
    Location
    indonesian
    MS-Off Ver
    O365
    Posts
    44

    Re: Interactive Charts/Dashboards

    when we open kbryant file, can you see bar chart with title chDashSelect, when we click button financial or Human resources, sales, etc

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. small enquiry about dashboards and interactive charts..
    By ZOH in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-15-2015, 07:57 PM
  2. Interactive Charts
    By hyattj in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-16-2013, 04:14 PM
  3. Interactive charts
    By espengrafik in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-17-2013, 12:46 PM
  4. interactive charts
    By danhenshy23 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 11-08-2010, 04:22 AM
  5. Interactive Charts
    By daigle in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 05-24-2006, 01:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1