Dynamic Hyperlink shapes

[electricmice]

I am trying to setup a hover on a shape such that it pops up a hyperlink similar to the usain bolt sheet.


https://www.dropbox.com/s/dqpflvdxjacv6fe/forum_choropleth_sample.xlsm

 

[NARAYANK991]

Hi ,


I don't understand what you want. Can you please explain , which shape , what hyperlink ?


Narayan

 

[electricmice]

When you mouse over one of the states, i would like to popup the state information (revenue, conversions, visits, state name).

 

[mercatog]

Hi

Try this code


Code written in the module Map. It adds a hyperlink for each shape (state) with the informations in a popup (ScreenTip) and updates the color of map

[pre]

Sub Popup()
Dim Shp As Shape

With ActiveSheet
For Each Shp In .Shapes
If Left(Shp.Name, 2) = "S_" Then
.Hyperlinks.Add Anchor:=Shp, Address:="", ScreenTip:=Info(Mid(Shp.Name, 3))
End If
Next Shp
End With
UpdateMap
End Sub

Private Function Info(ByVal Str As String) As String
Dim Res As String
Dim c As Range

Application.ScreenUpdating = False
If Str <> "" Then
Set c = Worksheets("Data").Range("D5:D52").Find(Str, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Res = "=[" & c.Offset(0, -1) & "]=:" & vbNewLine
Res = Res & " - Conversion: " & c.Offset(0, 1) & vbNewLine
Res = Res & " - Visits: " & c.Offset(0, 2) & vbNewLine
Res = Res & " - Revenue: " & Format(c.Offset(0, 3), "Currency")
Set c = Nothing
End If
End If
Info = Res
End Function

[/pre]

Click right on your dropdown in worksheet US Map and choose the mAcro Popup

 

[electricmice]

That works great! Is there any way to instead of displaying a tooltip, to instead display a shape similar to the usain bolt sheet (which allows me to stylize the popup a bit more.


Thank you for your help.


-Justin

 

[electricmice]

The other thing I noticed is that it does not update when the map updates via the dropdown menues on Map. How can I make it refresh with any change in data within the data table.

 

[mercatog]

Right Click on you dropdown Then Assign a macro and choose popup.

 

[Jordan]

Justin, if I understand correctly, you're looking for a mechanism to popup information about a state when your mouse moves into its region? Using "rollovers" or "interactive hyperlinks" (I call them the former, Chandoo the latter), you can do this as is shown in the file below:


https://docs.google.com/open?id=0B1OBNnu3ZbL0VXFvZFFCa1J3UTA


I'll tell you now that the whole thing is a bit complicated. For that reason, I haven't written about it even for my own blog. But I'm happy to explain how it's implemented should you want to endeavor down this path.