I’ve been trying to improve my Excel choropleth map spreadsheet from my first post.
The first thing I tried was to try to update it using some maps created on ClearlyandSimply.com. So I’ve created two new versions, one for Europe and one of the World. Both using the maps from ClearlyandSimply.com, with a few small alterations to the code so that on hovering over the map it tells you the country not the abbreviation. I did try to make my own UK maps of Constituency boundaries and Counties but using this technique but no matter how hard I tried I couldn’t get it to work. If you do get it to work, please let me know. Note that in both the Europe and World map the data may not be accurate and is only there for illustrative purposes.
At some point my aim is to look into either actual open source GIS software or mapping in R using something like this but I like these Excel ones because they are easy to share around at work and play with for people with limited coding skills (like me).
After reading this post it looks like there are a few additions I could make to my spreadsheets.
- Highlight the shape that has been selected
- Have tables/charts which also update when the country has been selected
- I already have tables that update so it can’t be that hard to add charts that update too.
- Select the country to look at from a list of countries.
For the first point I just want to add a shadow to the shape that I have selected. This is done by updating the VBA on the data sheet from
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("shapelist")) Is Nothing Then Sheets("Map").Range("TableValue").Value = Target Sheets("Data").Range("A1").Select Sheets("Map").Select End If End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("shapelist")) Is Nothing Then Country = Application.WorksheetFunction.VLookup(Target, Range("Data_Lookup"), 2, False) Sheets("Map").Range("TableValue") = Country Sheets("Data").Range("A1").Select Sheets("Map").Select 'Reset all other shapes Dim shape As Range For Each shape In Range("Shapelist") With Sheets("Map").Shapes(shape.Value).Shadow .Visible = msoFalse End With Next shape 'Change selected shape With Sheets("Map").Shapes(Target).Shadow .Type = msoShadow25 .Visible = msoTrue .Style = msoShadowStyleOuterShadow .Blur = 2 .OffsetY = 3 .RotateWithShape = msoFalse .ForeColor.ObjectThemeColor = msoThemeColorText1 .Transparency = 0.25 .Size = 115 End With 'Make sure the shape is at the front Sheets("Map").Shapes(Target).ZOrder msoBringToFront End If End Sub
Upon clicking on the shape this:
- Gets the country name from the data
- Updates the table on the map sheet to give the KPIs for that country
- Resets all of the shadows for the countries
- Sets the shadow for the selected country with the settings I wanted
- Brings that country to the front
I have now added in a chart which updates when you click on the desired shape. I’m not going to do it the same way as on clearly and simply because it doesn’t make sense to have a list of 183 countries as that is too many. Adding the chart required no extra VBA, just the addition of an extra chart control sheet with some simple formulas.
The ranking to create this chart makes a unique rank. That is if you have three numbers that are all the same, e.g. 1, 1, 1, then their ranks will all be different, e.g. 1, 2, 3. This is done using the countif function.