| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). 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
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Show shape on cell select, Hide shape when cell is deselected | Excel Programming | |||
| How seperate text with comma in a cell into seperate rows | Excel Programming | |||
| Adding a hyperlink to a shape | Excel Programming | |||
| How do I seperate data from a pivot into seperate worksheets? | Excel Discussion (Misc queries) | |||
| how do I display a hyperlink in a seperate cell in excel? | Excel Discussion (Misc queries) | |||
Linear Mode
