Hyperlink Cell to Shape on seperate worksheets

R

Rick S.

I am struggling with this.
Here is my attempt to generate hyperlinks from a Cell (Sheet1) to a Shape
(Sheet5).
Code: 
Option Explicit
Dim wksData As Worksheet
Dim wksShps As Worksheet
Dim Shp As Shape
Dim rLook As Range
Dim rFind As Range
Dim shtCell
Dim shtRange
Dim shtLastRow
Dim sBalloon As String

Sub NumberToBalloon()
'Contributing author: shg
'create hyperlink from column D bubble number (sheet1) to balloon shape
(sheet5) _
by text value
'Reverse of macro BalloonToNumber (author: shg)

Sheets(1).Activate
Sheets(1).Hyperlinks.Delete

For Each shtCell In shtRange
If IsNumeric(shtCell) Then
'msgbox allows stepping thru each cell to visually see if hyperlink _
is created, then user can stop macro if they want to (for testing)
For Each Shp In wksShps.Shapes
If Shp.AutoShapeType = msoShapeOval Then
Set rFind = shtCell.Find(what:=Shp.TextFrame.Characters.Text)
If Not rFind Is Nothing Then
shtCell.Activate
MsgBox shtCell    'for testing
'creates a hyperlink
wksData.Hyperlinks.Add Anchor:=shtCell, _
Address:="", _
SubAddress:=Shp.Name  'creates an invalid
reference
End If
End If
Next Shp
End If
'code removed
Next shtCell
End Sub

The above does create a hyperlink, allthough it is invalid. The "Shape"
doesnt seem to have some type of "location" holder when the shape is on top
of another shape (a large image), just a name and the name doesnt aid in
creating a hyperlink or the hyperlink code is simply wrong.

Any hints, tips or examples are welcome.
Cross posted at: (with file attachment
http://www.excelforum.com/excel-programming/717551-hyperlink-cell-to-a-shape.html
 
G

Gary''s Student

You can accomplish this indirectly. Set the hyperlink to some cell, say B9,
on Sheet5.

Then in Sheet5 include the following Event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
Call PickShape
End Sub


and in a standard module something like:

Sub PickShape()
ActiveSheet.Shapes(1).Select
End Sub


Here is the scenario:

1. click on the hyperlink takes us to Sheet5, cell B9
2. getting to cell B9 activates the event macro whick takes us to the shape.

Just remember that the event macro must go the the worksheet code area.
 
R

Rick S.

Thank you Gary, I will give this a go.
--
Regards

Rick


Gary''s Student said:
You can accomplish this indirectly. Set the hyperlink to some cell, say B9,
on Sheet5.

Then in Sheet5 include the following Event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
Call PickShape
End Sub


and in a standard module something like:

Sub PickShape()
ActiveSheet.Shapes(1).Select
End Sub


Here is the scenario:

1. click on the hyperlink takes us to Sheet5, cell B9
2. getting to cell B9 activates the event macro whick takes us to the shape.

Just remember that the event macro must go the the worksheet code area.
--
Gary''s Student - gsnu201001


Rick S. said:
I am struggling with this.
Here is my attempt to generate hyperlinks from a Cell (Sheet1) to a Shape
(Sheet5).
Code: 
Option Explicit
Dim wksData As Worksheet
Dim wksShps As Worksheet
Dim Shp As Shape
Dim rLook As Range
Dim rFind As Range
Dim shtCell
Dim shtRange
Dim shtLastRow
Dim sBalloon As String

Sub NumberToBalloon()
'Contributing author: shg
'create hyperlink from column D bubble number (sheet1) to balloon shape
(sheet5) _
by text value
'Reverse of macro BalloonToNumber (author: shg)

Sheets(1).Activate
Sheets(1).Hyperlinks.Delete

For Each shtCell In shtRange
If IsNumeric(shtCell) Then
'msgbox allows stepping thru each cell to visually see if hyperlink _
is created, then user can stop macro if they want to (for testing)
For Each Shp In wksShps.Shapes
If Shp.AutoShapeType = msoShapeOval Then
Set rFind = shtCell.Find(what:=Shp.TextFrame.Characters.Text)
If Not rFind Is Nothing Then
shtCell.Activate
MsgBox shtCell    'for testing
'creates a hyperlink
wksData.Hyperlinks.Add Anchor:=shtCell, _
Address:="", _
SubAddress:=Shp.Name  'creates an invalid
reference
End If
End If
Next Shp
End If
'code removed
Next shtCell
End Sub

The above does create a hyperlink, allthough it is invalid. The "Shape"
doesnt seem to have some type of "location" holder when the shape is on top
of another shape (a large image), just a name and the name doesnt aid in
creating a hyperlink or the hyperlink code is simply wrong.

Any hints, tips or examples are welcome.
Cross posted at: (with file attachment)
http://www.excelforum.com/excel-programming/717551-hyperlink-cell-to-a-shape.html
Click to expand...
Click to expand...
 
R

Rick S.

Your suggestion worked out.
Thanks.
--
Regards

Rick


Gary''s Student said:
You can accomplish this indirectly. Set the hyperlink to some cell, say B9,
on Sheet5.

Then in Sheet5 include the following Event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
Call PickShape
End Sub


and in a standard module something like:

Sub PickShape()
ActiveSheet.Shapes(1).Select
End Sub


Here is the scenario:

1. click on the hyperlink takes us to Sheet5, cell B9
2. getting to cell B9 activates the event macro whick takes us to the shape.

Just remember that the event macro must go the the worksheet code area.
--
Gary''s Student - gsnu201001


Rick S. said:
I am struggling with this.
Here is my attempt to generate hyperlinks from a Cell (Sheet1) to a Shape
(Sheet5).
Code: 
Option Explicit
Dim wksData As Worksheet
Dim wksShps As Worksheet
Dim Shp As Shape
Dim rLook As Range
Dim rFind As Range
Dim shtCell
Dim shtRange
Dim shtLastRow
Dim sBalloon As String

Sub NumberToBalloon()
'Contributing author: shg
'create hyperlink from column D bubble number (sheet1) to balloon shape
(sheet5) _
by text value
'Reverse of macro BalloonToNumber (author: shg)

Sheets(1).Activate
Sheets(1).Hyperlinks.Delete

For Each shtCell In shtRange
If IsNumeric(shtCell) Then
'msgbox allows stepping thru each cell to visually see if hyperlink _
is created, then user can stop macro if they want to (for testing)
For Each Shp In wksShps.Shapes
If Shp.AutoShapeType = msoShapeOval Then
Set rFind = shtCell.Find(what:=Shp.TextFrame.Characters.Text)
If Not rFind Is Nothing Then
shtCell.Activate
MsgBox shtCell    'for testing
'creates a hyperlink
wksData.Hyperlinks.Add Anchor:=shtCell, _
Address:="", _
SubAddress:=Shp.Name  'creates an invalid
reference
End If
End If
Next Shp
End If
'code removed
Next shtCell
End Sub

The above does create a hyperlink, allthough it is invalid. The "Shape"
doesnt seem to have some type of "location" holder when the shape is on top
of another shape (a large image), just a name and the name doesnt aid in
creating a hyperlink or the hyperlink code is simply wrong.

Any hints, tips or examples are welcome.
Cross posted at: (with file attachment)
http://www.excelforum.com/excel-programming/717551-hyperlink-cell-to-a-shape.html
Click to expand...
Click to expand...
 
Top