• Excel Hyperlink Using MS Access VBA Code

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Excel Hyperlink Using MS Access VBA Code

    Author
    Topic
    #495130

    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.

    Viewing 1 reply thread
    Author
    Replies
    • #1456234

      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 :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

    • #1456330

      That seems to be working.

      THANK YOU!

    Viewing 1 reply thread
    Reply To: Excel Hyperlink Using MS Access VBA Code

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: