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,

 

[jim may]

I modified the previous Macro to have your Hyperlink jump to another sheet versus your home sheet. Read code carefully - Note I sent you to Sheet3 (from Sheet1).

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
On Error GoTo M
Dim i As Long
Dim j As Long
Dim MySheet As String
If Target.SubAddress <> "Sheet3!X20" Then Exit Sub
MySheet = Left(Target.SubAddress, WorksheetFunction.Find("!", Target.SubAddress) - 1)
Application.Goto Reference:=Worksheets(MySheet).Range("X20"), 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]

So I just caved and used the first code that requires a double click to jump to cell, but I have an issue. I cant seem to change the cells text. It must be the cell its referencing to jump to. The reason I want this is because I have multiple tables that span pretty far on my sheet. I wanted a frozen column to the left that just has each tables names, and when you click it, it takes you to that group.

 

[My Aswer Is This]

Please be specific:

You said:

I wanted a frozen column to the left

What column do you want to click on

So you enter Dad in range("A1")
And when you double click on Dad you want to be taken to Table Named Dad
Is that what you want?
​

 

[My Aswer Is This]

Assuming your want to double click on a Table Name in column A
Use this script.

See at top of this script it says
If Target.column=1

If you want to use some other column Change 1 to what you want.

If you double click on A4

And A4 has a table name then you will be taken to that Table

Even if the Table is on another sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
'Modified  2/18/2019  11:16:25 AM  EST
On Error GoTo M
Application.Goto Target.Value, Scroll:=True
With ActiveWindow
            i = .VisibleRange.Rows.Count / 2
            j = .VisibleRange.Columns.Count / 2
            .SmallScroll Up:=i, ToLeft:=j
        End With
End If
Exit Sub
M:
MsgBox "You double clicked on  " & Target.Value & vbNewLine & "That is not a table Name"
End Sub

 

[My Aswer Is This]

You seem to keep saying all you want to do is click not double click.
OK lets look at this situation.
You enter all your Table names in Column A
Then you install your script and all works well when you click on the table name.
Now next week you want to enter more Table names in column A
So you click on a empty cell in column A so you can enter the Table Name.
And the minute you click on the empty cell in column A presto the script runs and the script errors out because you do not have a table named nothing.

Maybe you would like to explain why double clicking is a problem and I can come up with another solution.

We could do all this by having you enter table names in column A
Then click on the table name. Then click on a button to run the script if you thought that would be better.

Or we put all the table names in a Activex Combobox
Then when you select the Table name in the Combobox the script runs.

 

[gorillawar]

The reason I wanted single click is because this isn't for it, its for another user. They like very simple actions, and a single click falls into that (I wouldn't care if its a single click or not).

Currently I am just using normal hyperlinks. I have severalcells in column A that are hyperlinked to other cells in the sheet when I clickthem, and this column is frozen so you can still click each cell while viewingthe different tables. They are each named with display text as the table it istaking you to. Basically how a table of contents works in a word document.
I will just use the double click option,since it seems the simplest, but I still need the ability to change the cellstext.



Thanks,

 

[My Aswer Is This]

Well see you never mentioned in your orginal post that the links were to Tables and you never mentioned they would all be in Column A.

So are you saying all the links you want to click on are in column A of the same sheet??

And if you click on or double click on John you will want to be taken to a table named John Correct ??


And there will be nothing in column A but table names is this correct ??

And you do understand this will require Vba and the script will have to be installed ??

Does your friend know how to do this?

If your friend needs every thing very simple and he cannot understand double click will he know how to install a Vba script?

So my previous post sent today does exactly that but requires double click

You put John into A4 or A5

Or A99 and when you double click on John you will be taken to a table named John.

Did you try my last script sent earlier Today??
Did it work??

If not what did it do ?

And why is it I sent you a new script earlier today and you never mentioned if this worked??

And please answer all my questions or helping you will take a long time.

 

[My Aswer Is This]

If you want to use click and not double click use this script.

Enter Table Names in column A

Then if in A1 You have "George" and you click you will taken to Table named George

Now if you click any place in column A where their is text but is not a Table Name you will get a error message

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  2/18/2019  8:47:05 PM  EST
If Target.Column = 1 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Application.Goto Target.Value, Scroll:=True
With ActiveWindow
            i = .VisibleRange.Rows.Count / 2
            j = .VisibleRange.Columns.Count / 2
            .SmallScroll Up:=i, ToLeft:=j
        End With
End If
Exit Sub
M:
MsgBox "You clicked on  " & Target.Value & vbNewLine & "That is not a table Name"
End Sub

 

[gorillawar]

This last one will work fine.

Thank you

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

This last one will work fine.

Thank you