It is important for the VBA developer to understand how the shapes are arranged in a choropleth map in order to assign properly OnAction macros and create hyperlinks in them. But why are macros and hyperlinks needed in the first place ?

An interactive map dashboard can be enhanced with screen-tips shown while the cursor is hovering over countries and pop-up tables/charts displayed when a country is clicked. 

The best place to start after loading a workbook with maps made out of several free form shapes is the ribbon: Page Layout / Selection Pane.

It is immediately evident that several shapes are grouped to form a country e.g. Spain shown as ESP (Espana) is made of 4 shapes (mainland Spain and 3 islands).

Notes:

• OnAction macros can be assigned to grouped shapes. 
  
  
• But even if a grouped shape is clicked, thus triggering the OnAction macro, the application.caller property will always return the name of the individual shape under the cursor and not the name of the group. 
  
  
• Hyperlink screen-tips will NOT be displayed, if assigned to a grouped shape.
  
  

The VBA developer is left with no option other than to ungroup grouped shapes. The workbook below, which is provided as a proof of concept and as NOT an applied example, has several procedures used with this and the following articles, including one with the shape ungroup method. If the method is used with a shape that doesn't belong to a group, a runtime error will occur.

---

---

Sub ListUngroupedShapeNames()

    Dim oShp As Shape
    Dim r As Long
        r = 1
       
    For Each oShp In Sheets("Europe Map").Shapes
        If oShp.Type = msoGroup Then oShp.Ungroup
    Next oShp
    
    For Each oShp In Sheets("Europe Map").Shapes
        r = r + 1
        Sheets("List").Cells(r, 4) = oShp.Name
    Next oShp
    
    Set oShp = Nothing

End Sub

---

choropleth_map_europe_hyperlinkonactiondemo.xlsm
File Size:	122 kb
File Type:	xlsm

Download File

---