Hyperlink Cell to Shape on seperate worksheets
 

I am struggling with this.
Here is my attempt to generate hyperlinks from a Cell (Sheet1) to a Shape
(Sheet5).
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-prog...o-a-shape.html
--
Regards

Rick

Hyperlink Cell to Shape on seperate worksheets
 

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." wrote:

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-prog...o-a-shape.html
--
Regards

Rick

Hyperlink Cell to Shape on seperate worksheets
 

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

Rick


"Gary''s Student" wrote:

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." wrote:

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-prog...o-a-shape.html
--
Regards

Rick

Hyperlink Cell to Shape on seperate worksheets
 

Your suggestion worked out.
Thanks.
--
Regards

Rick


"Gary''s Student" wrote:

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." wrote:

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-prog...o-a-shape.html
--
Regards

Rick