View Full Version : Excel Heat Map with VBA
towely321
09-07-2015, 06:34 PM
I need to make an interactive risk management heat map that allows the user to filter data for 5 fields that are in my spreadsheet. Month, year, company, risk, and risk subcategory. My data also includes the impact and probability of each type of risk. In the same interactive/dynamic way a pivot table works, I need the filtered data to be displayed in a heat map like so...with probability and impact as the axis (axi?) and the points labeled with the type of risk. I'm thinking VBA will be needed but my VBA skills are awful. I left some sample data below.
Does anybody know where I can start or have any ideas? Thanks!
14342
Month
Year
Company
Risk
SubRisk
Impact
Prob
May
2010
A
MM
MM-1
1
.3
May
2009
C
HH
HH-1
2
.34
Jan
2009
A
MM
MM-2
4
.53
Nov
2009
D
PP
PP-2
2
.12
mikerickson
09-07-2015, 07:00 PM
That looks like a scatter graph Chart with the background set to Gradient.
I'm curious how the graph got 7 data points with only 4 rows of data.
towely321
09-08-2015, 04:33 AM
They graph and data aren't related. It was just to show what I'm looking for
towely321
09-08-2015, 09:48 AM
Maybe this will help. From this example pivot, I want to filter the Month, Year, Company and risk. Once it is filtered, I would like to plot the subtotals (in this case) Total M1 and Total M2 (Which are the averages) on a scatter plot like before. And then also be able to do the same for different filters and the different types of risk.
14345
Paul_Hossler
09-09-2015, 06:47 AM
Can you post a WB (make up the data) with 20-30 lines of data?
I assume that the GYR background is just a picture and has nothing to do with any data values?
towely321
09-09-2015, 07:57 AM
Do I just add the WB as an attachment?
Paul_Hossler
09-09-2015, 08:28 AM
Click [Go Advanced] bottom right
But here's an example
A scatter plot can't be a Pivot Chart so I ran the selected data into a hidden temp sheet
The 'Data' just has some made up date, the 'PivotData' is a PT with slicers, and the 'DataChart' has the generated scatter based on the PT data copied to the hidden 'Temp' sheet
towely321
09-09-2015, 08:59 AM
14352
towely321
09-09-2015, 09:03 AM
This essentially does what I want. Thank you so much. Hopefully I'll be able to play around with it and figure out the logistics of the code.
Paul_Hossler
09-09-2015, 10:40 AM
The only used code is an event handler on the 'PivotTable' sheet (Module1 was some recorder code -- ignore it)
Look at WS Temp but it's just a Copy/PasteSpecial of the pivot table
Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim rTemp As Range
Application.ScreenUpdating = False
Worksheets("Temp").Range("A:D").Clear
Target.TableRange2.EntireColumn.Copy
Worksheets("Temp").Visible = xlSheetVisible
Worksheets("Temp").Select
Worksheets("Temp").Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Worksheets("Temp").Visible = xlSheetHidden
Application.CutCopyMode = False
Set rTemp = Worksheets("Temp").Range("C:D").SpecialCells(xlCellTypeConstants, xlNumbers)
Application.ScreenUpdating = True
Sheets("DataChart").Select
Charts(1).SetSourceData Source:=rTemp
End Sub
towely321
09-09-2015, 10:55 AM
Thank you. Is there a way to label the data points with the subrisk labels. I kind of know how but the labels that I'm getting are "Series 1" and "Series 2", whereas I want them to say "M2" or "M2".
Also, is it possible to set all the filters first and then click a button to display the graph rather than the graph popping up everytime you change a filter? Thanks again!
Paul_Hossler
09-09-2015, 06:04 PM
Try this
There are some assumptions about PT format, etc.
If you make the wrong kind of change, it will probably not generate the right answer
towely321
09-10-2015, 09:19 AM
Paul, you're a life saver. Thank you very much. I should be able to take it from here.....maybe. On Wisconsin!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.