Results 1 to 10 of 10

Create hyperlinks with Shapes

Threaded View

  1. #1
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Ladson SC
    MS-Off Ver
    Office365
    Posts
    1,177

    Create hyperlinks with Shapes

    This code finds and matches the Text value of a Shape to the value of a cell on seperate worksheets within the same workbook.
    Sub bbb()    'Creat hyperlinks to shape by text value
    Dim shpTemp As Shape
    Dim ShtRange   'ShtRange
    Dim ShtLastRow    'ShtLastRow
    Dim ShtCell    'ShtCell
    
    
    If Left(ActiveWorkbook.Name, 3) = "MOT" Then
        With Sheets("Sheet1")    'Loop thru Column D
            ShtLastRow = .Cells(Rows.Count, "D").End(xlUp).Row
            Set ShtRange = .Range("D10:D" & ShtLastRow)
        End With
    End If
    If Left(ActiveWorkbook.Name, 6) = "AS9102" Then
        With Sheets("Sheet3")    'Loop thru Column A
            ShtLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
            Set ShtRange = .Range("A6:A" & ShtLastRow)
        End With
    End If
    
    For Each shpTemp In ActiveSheet.Shapes
        For Each ShtCell In ShtRange
            If shpTemp.AutoShapeType = msoShapeOval Then
                With shpTemp.TextFrame
                    If .Characters.Text = ShtCell Then
                        MsgBox .Characters.Text & " " & ShtCell    'for testing
                        MsgBox shpTemp.Name & " " & ShtCell    'for testing
                        'Create hyperlinks?
                        ActiveSheet.Shapes(shpTemp.Name).Select
                        ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), _
                                Address:="", SubAddress:=ShtCell
                    End If
                End With
            End If
        Next ShtCell
    Next shpTemp
    End Sub
    Questions:
    How can I write code to create a hyper link from the Shape to the cell? If the user clicks on the shape it links to a cell and vise~versa.
    My attempt above fails, it seems to go thru the first found shape but generates an error after the second found shape.
    No hyperlinks are created.

    Run-time error '5':
    Invalid procedure call or argument
    Last edited by Rick_Stanich; 02-09-2010 at 02:12 PM. Reason: Better title
    Regards

    Rick
    Win10, Office 365

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1