Hi all,
How can I re-colour a tree map (only available in Excel 2016) by using VBA? I have 5 sets of colours in my control sheet (G7:G16 - K7:K16) and want to change the colour whenever a different colour scheme is selected in my form control combobox. Below code works well for other charts, though, I don't seem to get it right for a tree map as it has more than 1 colour within one data series.
Any ideas?
Please Login or Register to view this content.
Hi, if you could post a file it will help.
I have not yet used this but seeing the sample code my guess it could be simple
One thing I notice is that myCol is a Long variable and you use ForeColor . RGD = ... have you tried ForeColor . Interior . Color = ... ?
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Was that the file 'Forum map.xlsb' you sent previously?
There you go...
I basically want the code to loop through the colour schemes in my control and apply the in the form control selected colour.
I'll check it out later today and see what I can do for you
Not much I can do with all the invalid references !!!
Ups... I had to delete a few things to make the file smaller, seems like I accidentally removed the specific range that I’m referring to in my code as well. Though, the code didn’t seem to work anyway, so just defining the colour range in the control sheet as well as the form control cell link should work? There should only be two defined ranges needed in total? so you could ignore all invalid ones.
Just pass the sheet name and the ranges it should cover
I am not going to work with assumptions ...
I I were you I would take a look here
https://exceloffthegrid.com/creating...arts-in-excel/
Unfortunately, I won’t have access to the file until tomorrow. That’s why I tried to explain that the other ranges don’t matter. Sorry about the confusion! But again, I simply want to change the colour scheme of the tree map from let’s say blue to grey, there’s no calculation or logic behind, just simply changing the colour shades. It should be a simple line of code but I couldn’t find the right syntax to do that. There won’t be any defined ranges needed. The code could also just refer to the cells in the control where the colours are located without giving these cells a name.Originally Posted by Keebellah
Just pass the sheet name and the ranges it should cover
I am not going to work with assumptions ...
I think you are confusing my post with a previous one about choropleth maps? I just want to change the colour of the chart, it’s not data related...
Here's the updated file...
I'm not the one confusing anything read your own title:
You get answers for what you are askingRe-colour tree map by using VBA
Yup. exactly! I want to change the colour of my tree map chart by using VBA. The link you posted focuses on how to create custom map charts by using VBA. I already use a built in Excel chart (new in Excel 2016), so I don't have to create one, I just want to change it's colour (which I can already do manually now) by using a Macro...
True but in the link vba code is shown that tweaked might help, I told you I have no idea so for me it's looking at how others do it and with some imagination put it to work for you example
It's a TIP not a solution
I'll see if I can help too but that takes time since I'm not 100% only into this one.
Sure, understand! was just hoping you know the right colour syntax for tree maps since you're using Excel 2016, most people aren't, so they cannot help. Changing colours of tree maps seems to be a slightly different line of code then for other more common charts...
The new charts use a slightly different chart engine to the standard ones and the vba object is not a complete match.
Try using the Legend.LegendEntries() object to alter the Format.Fill properties.
It's not clear from your example what colour should be applied to any particular point.
hmm.. not sure how to use Legend.LegendEntries()
In my sample file I have 5 different colour schemes, and I want to be able to switch between them. The darkest shades should always apply to the highest number and the lightest to the lowest (which is the Excel standard when using these new built-in tree maps). And since the chart is linked to my data table, it should automatically be formatted anyway...
This is just to show how to update the legened entries. I extended the colours to match the number of points.
If you want the colours to truely reflect values then you will need to calculate the colour to use in someway, but that is a different problem.
@esbencito: you see, there is also someone that like these challenges too, and that was the idea behind the link I posted for you.
With Andy's idea it nice to try out things and see where it gets you / us
We're never too old to learn and there is always something (new) to learn
PS The fact that I use Excel 2016 does not mean I know everything, the tip of the iceberg for me is microscopical seen all the options ...
Last edited by Keebellah; 12-08-2017 at 08:44 AM. Reason: xtra info
Hi guys, I must honestly say I don't really understand yet how a heatmap really works but thanks to Andy's idea and putting it together with some of my trial and error tests I came up with this.
BTW @esbencito, you only have 10 colors in the colour chart so only ten colours sohow up the other 10 are white just add the other colors (rows 7 through 26)
Curious to hear if this works for youPlease Login or Register to view this content.
WOW, I'm so happy you tell us if it has helped
Sorry, been travelling!WOW, I'm so happy you tell us if it has helped
Thanks both!! Just tried! It almost works as expected. Though, the code somehow adds a weird dotted pattern to the shapes? Can this be prevented? Also, the colours are applied 'backwards' it should use the darkest shade for the highest number not the other way around. What part of the code would I have to adjust to change that?
I don't know why the dotted pattern. probably a setting, you could add a pattern code too
The color is backwards? Well so are your colors. The scale you have is dark to light you can see that for yourself. So what you you think would be the solution?
Well start from light to dark. No rocket science here
I'm aware that I can just switch the colours in my control, but since I'm trying to learn how your code works, I wanted to see how such an adjustment would look like!
How would you add a pattern code to it? Something like the below?
Please Login or Register to view this content.
Like I mentioned, my code is based upon what Andy Pope posted and my own trial and error. so ....
You can do the same and ... trial and error.
I don't know if the code you added works, have YOU tried it?
If I have time some time later (today or tomorrow) I'll check that too but right now It's not possible for me to test your code
So make a copy of the file and try it out on that one if it messes up you can revert to the original.
I have tried several ways of course! It does not work! That’s why I’m asking
Then you should have mentioned that.
Like I said, I'll take a look and see what I can do.
The thing is you have to figure out how the pattern has to be addressed and I don't know that either, Google and a lot of patience and many tests
That's the way I will have to do it too, so you see? HeatMaps are abracabadra to me
Did a quick search and found this link
http://www.globaliconnect.com/excel/...=79&Itemid=475
modified the code and it seems to work
So you see it's just a question of searching and lookingPlease Login or Register to view this content.
Incredible!! Thanks! I did indeed try to search online, but since I still have little experience with code, I usually end up being more confused than before
Something I just realised... since this is a dashboard where numbers change depending on what is selected in the form control combo boxes, often you would end up with just 10 data points instead of 20, as there aren't more stores in certain countries. Your code would then just apply the first 10 colours (out of 20), whereas Excel, when choosing the standard colours, would apply the darkest shade to the highest number and then take e.g. the third darkest shade (instead of second) for the second highest number. See example below:
111111.PNG
nbgfb.PNG
It makes it very hard to see such subtle difference in colour (image 1), but not even sure if this solvable with code..?
I'm sure it's solvable with code, 99.99% of things can be solved with code.
You can change color shades based upon the starting default (dark) shade and have the shade value vary according to the rang of (in your case) the store value or combobox value you want it to depend on.
You'r initial question was answered (and solved).
Without some basic knowledge you will become (and stay) dependent on guys like us, and that you should avoid. I suggest you dig deeper and you'll come up with ideas too.
There are plenty samples, I picked the solution from a sample and Andy's input.
What you will need is a lot of time and patience and of course imagination. Don't be afraid to try it out, you can run through a macro line by line and see what it's doing.
Write down (in words) what you want the macro to do, that is what I do and then 'translate' it into code respecting syntax and dimensioning
If the value is greater than or equal to something then do this or this else if it's .... then .... otherwise ...
That's the way many of us started (I did, many years ago) this way
Record a macro and do the steps, the open the vba editor and see what it does and translate back to words so you can 'decipher' it.
Save vba code (export the module) so you can use it in other files.
Well, you've got a long way to go.
To come back to you question, yes, this would be doable but that will be writing an entire module which takes time.
If it's crucial then you should consider to check the payed service on this forum, I'm not a part of that, I'm just a curios vba programmer that likes to help where I can but full development is another thing, that I do in some occasions for extra pocket money (i'm retired
Okay, good to know it's solvable at least! Thanks for the advice, will try to adjust the code myself accordingly. And YES you're right, looong way to go for me
I cannot promise you results right away but if you attach a sample file with all the comboboxes (like the screenshot) and what you want to see exactly I'll see if I can look at at it in-between things and try and advise .
I have to lookup things too and it's just a question of formulating the correct question your will find answers and tips and how others have done it.
Here you go...
Select "CK" under Store Type as an example and you will end up with only 5-6 doors, the chart illustrates it, but the colour shades are pretty much the same as explained in the post above with the two images.
Okay, see it.
Like I said, I'll try and work o it when I have the time and the patience
You still use the color scheme in the Control worksheet, right?
Sure, no worries
Yes! same colour scheme!
Take a look het at the speadsheet guru site
https://www.thespreadsheetguru.com/b...l-colors-excel
using some of the data from the link I posted I made some minor modifications.
I do not use the color schemes just the fill color of the color name if you check that sheet you'll see that I made the fill color to what the name indicates.
You can just add another color and give it the name you like expand your data validation and it'll work
The macro code in the module Dashboard_ColourChart has been changed:
Please Login or Register to view this content.
Thanks!!! I did not work initially, but I adjusted your code slightly, now it does!
Below would not work since the SeriesCollection points don't vary. Only the data within the series changes from a number to "-".
Hence, I adjusted it to the below to count the number of cells that show "-" and thus should not count as a data point.Please Login or Register to view this content.
You see, I do try to learn and code myself as much as I canPlease Login or Register to view this content.
Btw, do you know if there is a way to make such a heatmap available in Excel 2010? Some of my clients are using older versions of Excel and thus cannot see the chart and the Macro returns an error (obviously) for them since the chart does not exist... After you said 99.9% of things are solvable with code, I thought this could be one of them too
You, see code is nothing more than imagination and text to code
I remember when programmers where telling me 'I won't try and explain because it's really complicated', well that's all b......t if you knwo the syntax and have the ideas it can be done.
About the 2010 question, I don't know, like I said i never used heatmaps I just code and this was my 'first encouter'
Have you Googled? Something like "Excel 2010 alternative for a Heatmap" ?
Maybe this article? (Google magic)
https://www.airweb.org/eAIR/techtips.../HeatMaps.aspx
Agreed! It's like learning Chinese, it seems impossible (to some) until it's done
Not exactly what I was looking for, the outcome is too different from the built in heatmaps in 2016...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks