Center Hyperlink

[gorillawar]

Hello,

I have multiple hyperlink in a tab to jump to specific cells that are far away(to avoid scrolling). When I use them, it moves the screen until that cell isjust barely showing, usually in the far right hand side. Can I possibly have itso the cell gets centered on my screen?

Thanks,

 

[My Aswer Is This]

Here is the only way I know to do this:

In the range where you would normally enter a Hyperlink put something like this

$Z$65

Now when using this Double click script when you Double click on the cell with

$Z$65​

You will taken to the range Z65 and it will be centered on the screen

Now this will only link you to Ranges on the active sheet.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  2/15/2019  12:39:25 AM  EST
Cancel = True
On Error GoTo M
Dim i As Long
Dim j As Long
Application.Goto Range(Target.Value), Scroll:=True
        With ActiveWindow
            i = .VisibleRange.Rows.Count / 2
            j = .VisibleRange.Columns.Count / 2
            .SmallScroll Up:=i, ToLeft:=j
        End With
Exit Sub
M:
MsgBox "Improper Range"
End Sub

 

[My Aswer Is This]

Now this script works the same. But will automatically put the cell address of the range you just left into the cell your now in. So by double clicking on that cell will take you back to the cell you were just in.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  2/15/2019  1:59:30 AM  EST
Cancel = True
On Error GoTo M
Dim i As Long
Dim j As Long
Dim ans As String
ans = Target.Value
Application.Goto Range(Target.Value), Scroll:=True
        With ActiveWindow
            i = .VisibleRange.Rows.Count / 2
            j = .VisibleRange.Columns.Count / 2
            .SmallScroll Up:=i, ToLeft:=j
        End With
Selection.Value = Target.Address
Exit Sub
M:
MsgBox "Improper Range"
End Sub

 

[jim may]

Great answer My Answer is this...

I sort of PIRATED your code and eliminated the need to use the Double-Click Event by utilizing the Follow_Hyperlink Event ONLY. What do you think? Below code does not Qualify the Macro's action for this SPECIFIC Hyperlink, which possibly be done by using the Application Intersect method, etc... Jim

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
On Error GoTo M
Dim i As Long
Dim j As Long
Set Rng = Range(Target.SubAddress)
Application.Goto Rng, Scroll:=True
        With ActiveWindow
            i = .VisibleRange.Rows.Count / 2
            j = .VisibleRange.Columns.Count / 2
            .SmallScroll Up:=i, ToLeft:=j
        End With
Exit Sub
M:
MsgBox "Improper Range"
End Sub

 

[gorillawar]

I prefer needing only one click to trigger, but I cant seem to get this one to work. I got the earlier line to work just fine. Do I need to do something different here?

 

[jim may]

Stepping thru the code, on what line are you having problems? I'm on my way out the door for lunch. Will check in afterards (1+ hours from now).
Jim

 

[gorillawar]

Well I don't know anything about code, but the action wont work. When I click the cell, nothing happens. The previous one from the firs response would work, but id like 1 click, not 2.

 

[jim may]

#1 ) Have you put the code provided into the Code Window of the specific Sheet that your Hyperlink is in?

#2 ) You need, in order to Step-Through the code (one line at a time) enter a breakpoint in the code. To do this
from within the VBE click somewhere in the code line "

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)".

Then, from the Menu Select "Debug" - then select "Toggle Breakpoint" - this will place a Red Dot in the Left Margin. With that set go to your Sheet and Click on the Hyperlink.

This will trigger your Macro and will cause it to STOP UNTIL you press the function key F8; with each time you press F8 a single line of code will run. If there is an error and

error message will appear​

 

[My Aswer Is This]

Jim Thanks for using Follow Hyperlink. I had never used this before and did not know it would be this easy.

Your script worked for me.

Is there a way to get this to work following a link to another sheet not just the active sheet.

 

[My Aswer Is This]

Well doing what you want on just a click would mean every place on your sheet if you click the script would try to run. Not sure why Jim's script is not working for you it works for me. And I'm surprised double clicking is not a good plan for you.

Well I don't know anything about code, but the action wont work. When I click the cell, nothing happens. The previous one from the firs response would work, but id like 1 click, not 2.