PDA

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!