Set up the Map
First, I found a clip art map of the continental USA, with a separate shape for each state, and pasted that into a workbook.
Next, named the shapes, using the two digit state abbreviation.
- Click on a state shape, e.g. Washington
- Click in the Name Box, and type the two digit code – WA
- Press the Enter key, to complete the naming.
This will be a good test of your geography knowledge and state abbreviation skills.
Set Up the List of Doctors
On a sheet named Doctors, I entered a list of doctor names, state codes and number of patients.
I named the range, DoctorList, using the formula:
as described in my Dynamic Named Ranges tutorial.
Set Up the Filter Criteria
On the Map sheet, I set up a criteria range for the filter, with the State heading in cell A1, and a State code in cell A2.
To name this range, I selected cells A1:A2 and typed Criteria in the Name box, then pressed the Enter key.
Set Up the Filter Extract
Next, I copied the headings from the Doctor list, and pasted them below the map.
With the pasted cells still selected, I named that range StateList.
This is where the list of doctors will appear when you click a state in the map.
Add the Filter Macro
Sub GetDoctorList() On Error Resume Next Dim strState As String Dim sh As Shape Dim wsMap As Worksheet Dim wsDoctor As Worksheet Dim wsList As Worksheet strState = Application.Caller Set wsMap = Worksheets("Map") Set wsDoctor = Worksheets("Doctors") wsMap.Range("A2").Value = strState wsDoctor.Range("DoctorList").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=wsMap.Range("Criteria"), _ CopyToRange:=wsMap.Range("StateList"), Unique:=False wsMap.Activate wsMap.Range("A1").Activate End Sub
Assign the Macro to Shapes
The final step is to assign the GetDoctorList macro to each shape. You could right-click on each shape, click Assign Macro, and select the macro, to do this manually. That might take quite a while for all 48 states, so I wrote a little macro, to do the work for me. You’ll only have to run this once, while setting up the workbook.
Sub AddMacro() Dim sh As Shape For Each sh In Worksheets("Map").Shapes sh.OnAction = "GetDoctorList" Next End Sub
Run the Filter
Now, to run the filter, click on a state in the map.
The state code appears in cell A2, and the list of doctors is in the extract range, starting in cell B18.