Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    385
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Hyperlink Using MS Access VBA Code

    What I am trying to do is create a Hyperlink on a sheet in a workbook that I am creating using MS Access and VBA code.

    I have the "Return to Contents" working on the individual tabs in the workbook.
    wksTemp.Cells(1, 1).Hyperlinks.Add anchor:=wksTemp.Cells(1, 1), Address:="", SubAddress:="'Contents'!A1", ScreenTip:="Click to Return to Contents", TextToDisplay:="Contents"

    What is giving me headaches is trying to set the hyperlinks on the Contents tab.
    I have about 121 rows and each one will hyperlink to the corosponding tab in the workboook. I could hard code each one, but that would require much time and would have to be modified each time a new company was added or removed from the list.

    Is there a way to modify the Hyperlink statement so that I can dynamiclly populate the SubAddress, ScreenTip, and TextToDisplay?


    Thank you for any help you can provide with this issue.
    Richard

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,381
    Thanks
    208
    Thanked 829 Times in 762 Posts
    Richard,

    See if this will fill the bill, seems to work for me.

    Code:
    Option Explicit
    
    Sub SetIndex()
    
       Dim shtData  As Worksheet
       Dim lRowCnt  As Long
       
       Sheets("Index").Activate
       [A2:A500].ClearContents
       lRowCnt = 2
       
       For Each shtData In ActiveWorkbook.Sheets
          If shtData.Name <> "Index" Then
            SetHyperLink lRowCnt, shtData.Name & "!A1", "Go To " & shtData.Name, _
                 "Click this link to goto: " & shtData.Name
          lRowCnt = lRowCnt + 1
          End If
       Next shtData
       
    End Sub
    
    Sub SetHyperLink(lRowCnt As Long, zSubAddress As String, _
                     zDisplayText As String, zScreenTip As String)
    
          ActiveSheet.Cells(lRowCnt, 1).Hyperlinks.Add Anchor:=ActiveSheet.Cells(lRowCnt, 1), _
                                      Address:="", SubAddress:=zSubAddress, _
                                      TextToDisplay:=zDisplayText, _
                                      ScreenTip:=zScreenTip
    
    End Sub  'SetHyperLink'
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    385
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That seems to be working.

    THANK YOU!
    Richard

Posting Permissions

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