Run vba by clicking shapehyperlink

[ybr_15]

Hi dear, I am still learning about hyperlinks (with shape). I have vba code in module to move from one sheet to another, like this

Sub HomeButton()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim rngReference As Worksheet
    Dim rngDestination As Worksheet
    Dim shpHyperlink As Shape
    
    Set rngReference = wb.ActiveSheet
    Set rngDestination = wb.Sheets("Home")
    Set shpHyperlink = rngReference.Shapes("Rectangle 1")
    
    rngReference.Hyperlinks.Add Anchor:=shpHyperlink, Address:="", SubAddress:="'" & rngDestination.Name & "'!B7", ScreenTip:="Back to Home"
End Sub

The code above work properly. Now, I want to add a new code so that when I click the hyperlink above (before moving to another sheet) then the second to fifth rows will be hidden

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim rngReference As Worksheet
    
    Set rngReference = wb.Sheets("Report")
    If rngReference.Rows("2:5").EntireRow.Hidden = False Then rngReference.Rows("2:5").EntireRow.Hidden = True
End Sub

But, I still didn't get what I wanted (second to fifth lines are not hidden in Report sheet before moving to Home sheet). I still confused here how to add or putt the code so I can get what I want. Thank you.....#Sorry for my bad english

 

[jmacleary]

Hi there. Try removing the IF clause, so the line:

    If rngReference.Rows("2:5").EntireRow.Hidden = False Then rngReference.Rows("2:5").EntireRow.Hidden = True

becomes:

rngReference.Rows("2:5").EntireRow.Hidden = True

 

[ybr_15]

Hi jmacleary, thanks for your reply. I have tried your suggestion but still can't. I know if the hyperlink is based on cell reference my code will run as I want (your suggestion too). But now, what I want to try is to use shape hyperlink to run my vba code. Can anyone help me? Thanks

 

[Jaafar Tribak]

The FollowHyperlink event doesn't run when the hyperlink is on a shape.

How about assigning a macro to the shape instead of an hyperlink ?

 

[ybr_15]

Hi Jaafar Tribak, sorry I don't know about that. I think because I can use hyperlink based on a cell reference so that I can do it too on a shape. I will try another ways as you suggest. Thanks for your information