VBA Hyperlink Not Working Properly

[RahulM]

Hello,

I have a macro to update data in different sheets of the same workbook by following the hyperlink.

It works perfectly for first hyperlink cell, however, for the rest; cursor moves from active hyperlink cell to a different cell when 'Selection.Hyperlinks(1).Follow' initiates the below-mentioned change event;

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

and it opens any random hyperlink.

I am unable to find the reason for this. Any help is appreciated.

===============================
Code to follow and open hyperlink:

Sub TestFollowHyperlink()

ActiveCell.Offset(0, 16).Select
Selection.Hyperlinks(1).Follow

Call UpdateandBack

End Sub

-------------------------------
Sub UpdateandBack()

Range("B3").Select

'Code to update the details

'Below code to return to Hyperlink cell
ActiveSheet.Range("A1").Hyperlinks(1).Follow

Cells(ActiveWindow.RangeSelection.Row, 1).Select

ActiveCell.Offset(0, 3).Select
ActiveCell.Offset(1, 0).Select

Call TestFollowHyperlink

End Sub

=======================================
Code from the sheet where data is updated

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim TargetIndex As String

TargetIndex = Target.Range.Row & "," & Target.Range.Column
'MsgBox TargetIndex

Sheets(1).Visible = True
Sheets(1).Activate
Sheets(2).Visible = False
End Sub

========================================
Code from the main sheet where Hyperlinks are mentioned

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim TargetIndex As String

TargetIndex = Target.Range.Row & "," & Target.Range.Column
'MsgBox TargetIndex

Select Case TargetIndex
'Cell address

Case "11,20"
Sheets(2).Visible = True
Sheets(2).Activate
Case "14,20"
Sheets(3).Visible = True
Sheets(3).Activate
Case "17,20"
Sheets(4).Visible = True
Sheets(4).Activate
Case "20,20"

End Select

Sheets(1).Visible = False

End Sub

========================================
Code from 'ThisWorkbook' module [from the sheet where data is updated]

Option Explicit
Private Sub Worksheet_Activate()
    Dim sh As Worksheet
  
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Manifest" Then
            sh.Visible = xlSheetHidden
        End If
    Next sh
End Sub

 

[PCosta87]

Hi,

Can you upload a sample file with some dummy data?
That would make debugging a lot easier.

Thanks

 

[RahulM]

Dear PCosta87,

Thank you for the reply.
I understand that the sample file would be more helpful but I cannot share the file due to work restrictions.

FYI, all the codes are mentioned in the post.

Thank you.

 

[PCosta87]

Dear PCosta87,

Thank you for the reply.
I understand that the sample file would be more helpful but I cannot share the file due to work restrictions.

FYI, all the codes are mentioned in the post.

Thank you.

Ok, I understand.

If I'm not missing anything, the only bit of code that is causing the cursor to move is:

ActiveCell.Offset(0, 3).Select
ActiveCell.Offset(1, 0).Select

This part moves the cursor 3 cells to the right and one cell down (which could be done with just "ActiveCell.Offset(1, 3).Select", by the way)...
Assuming you have your links in a column, this first line "ActiveCell.Offset(0, 3).Select" should not be there.

On a side note, how do you plan to stop the loop... since TestFollowHyperlink calls UpdateandBack and UpdateandBack calls TestFollowHyperlink I suspect the code will break when reaching a cell without any link to follow!
Shouldn't you be using loop statements instead?