Creating interactive map charts in excel is one of the easiest things to do…..that is if you know how. I recently created this map chart showing the top 50 tropical storms to hit the Unitef States since 1900. As you move ahead in time from 1900 to 2005, the map shows the states that were hit by the storm, the damage caused, storm category and rank. It takes about 5 lines of code per map unit (in this case a state) to render this effect.
You can download the file here.
Steps to create this interactive chart map in excel:
1. Get relevent data and lay it out clearly.
2. Prepare a placeholder for the map and the information points (Dates, Names, $’s etc.)
3. Introduce a dynamic control (in this case a horizontal scrollbar) that the user can use to interact with the graphic.
4. Insert formulae and charts so that when the user moves the dynamic conrtol, values get updated.
5. Inroduce the map chart (in this case the WMF map of the U.S.)
6. Write VBA code to make the relevent map objects behave in accordance with the user inputs.
(Data taken from Report by Pielke, Gratz, Landsea, Collins, Saunders and Musulin )
is there a step by step interactive map charts
June 23rd, 2009 at 9:41 pmTo go quicker, use the free tool BeGraphic.com
BeGraphic is an advanced data visualization add-in working inside Excel and PowerPoint. Its free version has dynamic maps and gauges.
10 000 additional maps are given away to business users (if they ask them using their pro email).
May 6th, 2010 at 3:05 pmI have edited your code to make it shorter and eisier (And a little bit efficient too):
Private Sub ScrollBar1_Change()
Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.Shapes(i1).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52
ActiveSheet.Shapes(i2).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52
ActiveSheet.Shapes(i3).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52
ActiveSheet.Shapes(i4).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52
ActiveSheet.Shapes(i5).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 52
On Error Resume Next
i1 = Application.WorksheetFunction.VLookup(Cells(27, 17).Value, Sheet2.Range(“stormlist”), 7, False)
ActiveSheet.Shapes(i1).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45
i2 = Application.WorksheetFunction.VLookup(Cells(27, 17).Value, Sheet2.Range(“stormlist”), 8, False)
ActiveSheet.Shapes(i2).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45
i3 = Application.WorksheetFunction.VLookup(Cells(27, 17).Value, Sheet2.Range(“stormlist”), 9, False)
ActiveSheet.Shapes(i3).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45
i4 = Application.WorksheetFunction.VLookup(Cells(27, 17).Value, Sheet2.Range(“stormlist”), 10, False)
ActiveSheet.Shapes(i4).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45
i5 = Application.WorksheetFunction.VLookup(Cells(27, 17).Value, Sheet2.Range(“stormlist”), 11, False)
ActiveSheet.Shapes(i5).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 45
Application.ScreenUpdating = True
Cells(27, 18).Select
End Sub
July 11th, 2012 at 7:24 am