Excel Hyperlink Using MS Access VBA Code

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

That seems to be working.

THANK YOU!

Reply | Quote