I am in favour of dashboard designs with click-able shapes. When both a hyperlink and an OnAction macro are attached to a shape, it seems that the hyperlink has higher priority, because the macro doesn’t run.
Screentips are a great visualization, as the user hovers over a choropleth map. There is an excellent article written by Robert Mundigl on interactive choropleth map dashboards. Unfortunately, map shapes couldn’t be clicked in his hyperlink enabled dashboard example.
To emulate the OnAction behaviour of a shape which has a screentip through the hyperlink route the following VBA tip can be used:
Screentips are a great visualization, as the user hovers over a choropleth map. There is an excellent article written by Robert Mundigl on interactive choropleth map dashboards. Unfortunately, map shapes couldn’t be clicked in his hyperlink enabled dashboard example.
To emulate the OnAction behaviour of a shape which has a screentip through the hyperlink route the following VBA tip can be used:
- Shapes should be ungrouped as shown in the first article.
- Hyperlinks should be assigned to every map shape using as SubAddress a unique cell address in the same worksheet with the map shapes in column A.
- The first hyperlink cell row should be assigned around the middle of the dashboard (e.g row 25) in order to avoid screen scrolling while a hyperlink is activated.
- Shape object positioning should be 'Don't move or size with cells' or oShp.Placement = xlFreeFloating
- There are 60 shapes in the European choropleth map. Rows 25-85 and column A should be hidden, so that the cursor is not visible during hyperlink activation.
- Unfortunately, the Workbook_SheetFollowHyperlink event seems functional with cell, but not with shape hyperlinks, so screen updating cannot be trapped.
- A Worksheet_SelectionChange event macro should be entered in the choropleth map sheet.
- The OnAction macro has lower priority than the hyperlink and cannot be used.
Sub AssignHyperlinksToShapes()
Dim oShp As Shape
Dim r As Long
r = 24 'row should be near the center of the dashboard
For Each oShp In Sheets("Europe Map").Shapes
'assign hyperlink only to country shapes
If Left(oShp.Name, 2) = "S_" Then
r = r + 1
With Sheets("Europe Map")
.Hyperlinks.Add Anchor:=oShp, _
Address:="", _
SubAddress:="'Europe Map'!A" & r, _
ScreenTip:=oShp.Name
End With
End If
Next
Set oShp = Nothing
End Sub
Dim oShp As Shape
Dim r As Long
r = 24 'row should be near the center of the dashboard
For Each oShp In Sheets("Europe Map").Shapes
'assign hyperlink only to country shapes
If Left(oShp.Name, 2) = "S_" Then
r = r + 1
With Sheets("Europe Map")
.Hyperlinks.Add Anchor:=oShp, _
Address:="", _
SubAddress:="'Europe Map'!A" & r, _
ScreenTip:=oShp.Name
End With
End If
Next
Set oShp = Nothing
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Select Case Target.Row
Case 25 To 85
MsgBox Target.Address
Case Else
MsgBox "Invalid hyperlink"
End Select
End If
End Sub
If Target.Column = 1 Then
Select Case Target.Row
Case 25 To 85
MsgBox Target.Address
Case Else
MsgBox "Invalid hyperlink"
End Select
End If
End Sub
How the VBA tip works:
- A screentip is shown while the cursor hovers above any map shape.
- If the user clicks on a map shape, the hyperlink is activated and triggers the “Worksheet_SelectionChange” event.
- Using the unique address of the activated cell, the country shape could be identified and a macro triggered.
- If the user clicks the same map shape twice, the worksheet event will not be triggered (the default behaviour), unless a cell outside the range of the hyperlinks is activated, at the end of the worksheet event.
- If a Textbox with No-Fill, No-Outline is inserted in front of a map shape, the same hyperlink has to be assigned to it, otherwise the screentip won’t be displayed in front of the text and the user won’t be able to activate the hyperlink, if the text is clicked.
| choropleth_map_europe_hyperlinkonactiondemo.xlsm |
