Using Excel Hyperlinks to refer to a Subroutine rather than a Cell

[ct60]

Hello Again -

I have found that given a list of worksheet names I can create hyperlinks to
those worksheets (let's say specifically Cell P2) using the following code:

ws.Activate

For row = tRow To bRow
ws.Cells(row, col).Select
With Selection
t2D = Selection.Value ' Note that the Cell value is the WS Name
subA = "'" & t2D & "'!P2"

.ClearContents
ws.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=subA, TextToDisplay:=t2D
End With
Next

I can also scroll a worksheet to exactly the right cell using:

Sub scroll2Column(wsName)
Sheets(wsName).Activate
ActiveWindow.ScrollColumn = 16
ActiveSheet.Range("P2").Select
End Sub

I would like to combine these two concpets and create a hyperlink which
doesn't just refer to the cell but to the subroutine which brings the user to
the cell and then conviently scrolls the worksheet as well.

Despite many attempts at this, I have not gotten that to work.

Does anyone know how this might be done?

Thanks in advance,

Chris Tauss ([email protected])

 

[Chip Pearson]

Try some code like the following:

Sub AAA()
Dim R As Range
Dim HL As Hyperlink
Set R = Range("C3")
Set HL = R.Hyperlinks.Add(anchor:=R, _
Address:=vbNullString, _
SubAddress:="'Sheet Three'!K128", _
TextToDisplay:="click for K28")
End Sub



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ct60]

Thanks Chip -

That will bring the user to the cell -- but not actually scroll the WS.

Ideally, I would like to add the scrolling aspect so I think I need to refer
to a subroutine rather than a cell ref.

I am just wondering if this is possible.

Best Regards,

Chris

 

[ct60]

Let me answer my own question.

There is a Workbook Event "Workbook_SheetFollowHyperlink" which allows for
code to be run after a hyperlink is clicked. So, I put in the following code
which does what I was hoping for:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target
As Hyperlink)
Dim rangeString As String
Dim rangeSubString As String
Dim whereExclaim As Integer
Dim scrollCol As Integer

rangeString = Target.SubAddress

whereExclaim = InStr(1, Target.SubAddress, "!")

If whereExclaim = 0 Then
Exit Sub
End If

rangeSubString = Mid(rangeString, whereExclaim + 1)

scrollCol = Range(rangeSubString).Column

ActiveWindow.ScrollColumn = scrollCol
End Sub

Note that, I also freeze the panes of each worksheet first then set the
scroll column. The net effect is that the data is lined up very nicely with
the frozen columns to the left and the headers at the top.

Nice - if I do say so myself.

Hope this helps.

Chris ([email protected])

 

[Chip Pearson]

If the hyperlink is on one worksheet and it points to a cell on
another sheet, Excel will scroll to the destination sheet when the
user clicks on the link. For example, with Sheet1 active, use the
code I provided and change the reference from "Sheet Three" to any
worksheet other than Sheet1. When click on the link on Sheet1, Excel
will take you to the worksheet that is the target of the link.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Héctor Miguel]

hi, guys !

copy/paste (or type)... the following in ThisWorkbook code module...

Dim fromHyperlink As Boolean
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
fromHyperlink = Target.Hyperlinks.Count
If fromHyperlink Then
ActiveWindow.ScrollRow = ActiveCell.Row
ActiveWindow.ScrollColumn = ActiveCell.Column
fromHyperlink = False
End If
End Sub

hth,
hector.