How to Edit Hyperlink Statement

[stuartgb100]

Hi,
I am using the following statement:

TargetSht.Range("B" & TargetRw).Hyperlinks.Add _
                        anchor:=TargetSht.Range("B" & TargetRw), Address:="", _
                        SubAddress:="'" & .Name & "'!" & .Range("B" & C.Row).Address, _
                        TextToDisplay:=SourceSht.Range("B" & C.Row).Value

When run, the new sheet window displays and the cell that I'm looking for is the activecell.
This could be in a row near the top, middle or near the bottom of the display.

Is there a way to make that row the first row in the display, please ?

Many thanks.

 

[myall_blues]

Add a line after your code:

Application.Goto Range("B" & TargetRw), True

 

[stuartgb100]

Thanks, works fine.
For my understanding:
Does that effectively assign a 'property' to the new hyperlink ?
Probably not correct terminology !

Thanks again.

 

[stuartgb100]

Actually, there is still an issue:

Before I run any code on my laptop, I have the VB Project window open on the left, and the Excel window open on the right.
So, I run the routine:
The code works well, and generates the hyperlinks from the values in SourceSht ("#Masters-Artists-,Albums,Tracks")
and pastes them as hyperlinks into the TargetSht ("#Artists-Index").
If I click any of the new hyperlinks in the TargetSht, it correctly brings up the relevent cell in the SourceSht, and that cell is activated.
All good`, so far.

However:
In the Excel window, the activecell can be anywhere (at the top, in the middle or at the bottom of the Excel window).
What I'd like to achieve is for the activececell.row to be displayed in the very 1str row of the Excel window.

Is that possible, please ?

Many thanks.

 

[myall_blues]

Apologies I think I misunderstood your initial request. If I understand correctly now, you want the target of the link to become the active cell(row) when you click the hyperlink. Is that correct?

If so, then add this code into ThisWorkbook (separate from your other code). To do that, right click on ThisWorkbook in the VBA window and click on View Code, then paste this code in the resultant window.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    Application.Goto Reference:=ActiveCell, Scroll:=True
End Sub

 

[stuartgb100]

Brilliant, many thanks.
Can you show me how to 'tweak' that a little, please ?

So the link opens the relevent sheet, and the target cell is positioned top left in the window.
If the cell is in col C, then cols A & B are not visible.
Is there a way to show all cols as well please ?

Thanks.

 

[myall_blues]

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    Application.Goto Reference:=Cells(ActiveCell.Row, 1), Scroll:=True
End Sub

 

[stuartgb100]

Ah, I see...........

Previously there was a reference to the row, now there is also one for the column.

Many thanks again.
Regards.

 

[myall_blues]

Actually previously there was reference to the whole cell, and now there is a reference to the row, and the 1st column.

Glad it’s sorted for you.

 

[stuartgb100]

Thanks for the education.
Cheers.