Excel Treeview - VBA Control
The new TreeView in Excel 2016 is great, however, code-base manipulation via VBA seems to be absent. Specifically looking to change the colors for each of the squares, or at least control how the chart defines its color base.
Please allow for vba coding for all the new chart types. Also, why is this the case? --> When you select older charts they are considered "ChartArea" while newer chart types are considered "ChartObjects".
Shuvro Basu commented
Yes you can do it but it's still a very cumbersome process. It's better to start off by recording a macro. This will have only 1 line of code, which is just what we need.
' Macro15 Macro
Dim pnts As Integer
Dim NEG_COLOR As Long
NEG_COLOR = RGB(255, 0, 0)
'The below line came from the macro recording
'******************* MY CODE FOLLOWS *********************
'In my method I find the Datalabel of the box I want to change color using the DataLabel text and then change the color.
I didnt use the loop to run the code, because I wanted to test it first. And it works!!!
'Use the below code to count the shapes in the treemap.
pnts = ActiveChart.FullSeriesCollection(1).Points.Count
'This is the core code
'Check the shape you want by comparing the datalabel text
If ActiveChart.FullSeriesCollection(1).Points(x + 1).DataLabel.Text = "Type-DataLabel-That-You-Want" Then
ActiveChart.FullSeriesCollection(1).Points(x + 1).Select
ActiveChart.FullSeriesCollection(1).Points(x + 1).Format.Fill.ForeColor.RGB = NEG_COLOR
Hope this helps someone.
Just to add to this, all of the new charts in 2016 do not include VBA support
Zack Barresse commented
I'm all for having the object model be current with the current resources in the application.
Yes.. sorry for the mixup on the name.. I've managed to figure it out, but it would have been much easier with documented properties and methods of the object. Also, any changes made to the chart while recording Macro's are completely not-recorded.