Run code if hyperlink clicked falls in target range

[vanhunk]

Run code if hyperlink clicked falls in target range:
I have hyperlinks in different columns on the workbook sheets. If I click on a hyperlink in column B of any sheet I want the target screen to scroll so that the destination cell is in the upper left corner of the screen. If I click on any other hyperlink, I don't want any scrolling taking place.

I am thus looking for something that would work like Intersect. I.e. if the cell containing the hyperlink is in any column B, scroll the destination screen, if not don't.

Current code is working fine for hyperlinks in any column B, but unfortunately also kicks in for hyperlinks in other locations:

Option Explicit
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

 ActiveWindow.ScrollRow = WorksheetFunction.Max(Selection.Row - 4, 1)

End Sub

Regards,
vanhunk

[SamT]

Replace that code with

Option Explicit

Public DoScroll As Boolean 'Set in sheet ??? 'Edit comment to suit

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

 If DoScroll Then ActiveWindow.ScrollRow = WorksheetFunction.Max(Selection.Row - 4, 1)
  DoScroll = False
End Sub

And put this in the sheet module with the hyperlinks

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'DoScroll declared in ThisWorkbook

If Not Intersect(Target, Range("B2:B" & Cells(Rows.Count,"B").End(xlUp).Row)) Is Nothing Then 
'Adjust top Row (2) of Range as appropriate.

DoScroll = True
Else: DoScroll =False
End If

End Sub

[vanhunk]

Thank you SamT:

PROBLEM: It does however not work as intended.
I have many sheets with the same structure.
In the "B" columns I have hyperlinks that take you to another sheet in the same workbook. (Click on hyperlink once).
In other columns I have hyperlinks that open Websites.

When clicking on hyperlinks in "B" columns the destination sheet must be scrolled so that the destination cell is in the top left corner of the destination sheet.
When clicking on hyperlinks in any other column, no scrolling must take place.

I have tried the following code in ThisWorkbook module but get an error message of "Run-time error '13': Type mismatch"

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

       If Not Intersect(Target, Columns("B:B")) Is Nothing Then
       
          MsgBox "You clicked Hyperlink in Column B", vbExclamation

          ActiveWindow.ScrollRow = WorksheetFunction.Max(Selection.Row - 4, 1)

       End If
End Sub

Regards,
vanhunk

[SamT]

No, I have to apologise. All my reference files are on a broken computer and I have to extrapolate from the rest of VBA to Hyperlinks.

Try this. It works in Excel 2002

Option Explicit

Dim DoScroll As Boolean


Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If DoScroll Then ActiveWindow.ScrollRow = WorksheetFunction.Max(Selection.Row - 4, 1)
DoScroll = False
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Hyperlinks.Count Then
  If Intersect(Sh.Range("B:B"), Target) Is Nothing Then
    DoScroll = False
  Else: DoScroll = True
  End If
End If

End Sub

The FollowHyperLink event sub refers to the cell containing hyperlink, not the link_target, but occurs after the link is followed

[vanhunk]

Thank you SamT,
It is almost working as expected.
It is doing something unexpected which I couldn't figure out why.
I have an Index with links to information on the sheets.
When I click on these links they take me to where I want to be and also do the scrolling bit as I wanted.
However, when I click on hyperlinks (they open up web pages) in other columns the screen also scroll up. I don't want that to happen.
I.e. when a hyperlink is clicked in any column other than "B", no scrolling should take place - which is not the case at the moment?!?

Regards,
vanhunk

[SamT]

However, when I click on hyperlinks (they open up web pages) in other columns the screen also scroll up. I don't want that to happen.
I.e. when a hyperlink is clicked in any column other than "B", no scrolling should take place - which is not the case at the moment?!?

I knew that excel sheets would scroll at least till the Linked to cell was visible, but that is a more difficult issue to deal with. I don't know why web pages are scrolling.

Is it acceptable that the link_targets are always scrolled to the top of the page?

Please show us one of the hyperlinks to a web page that is not supposed to scroll.




I don't know when I can get to this, so if anybody wants to contribute, feel free.

[vanhunk]

Thank you SamT, I have uploaded an example file. Yes the scrolling does matter. If I click on one of the links (not in "B") lower down, I don't want scrolling to happen.

Regards,
vanhunk

[SamT]

Any scrolling that happens in a web browser is the result of the browser.

FYI, none of the links caused any scrolling in FireFox on my computer.

[vanhunk]

Any scrolling that happens in a web browser is the result of the browser.

FYI, none of the links caused any scrolling in FireFox on my computer.

Hi SamT,
Thank you very much. The scrolling happens before the browser even opens. I have attached another file with explanations of what exactly happens.

Kind Regards,
vanhunk