How can I insert a Row by clicking on Hyperlink in VBA

Question

My code is not working, I am trying to insert a Row by clicking on Hyperlink and My hyperlink I am using + symbol...

     ActiveSheet.Cells(1, 1).Value = "+"
       If ActiveSheet.Cells(1, 1) <> "" Then
         ActiveSheet.Hyperlinks.Add Anchor:=Cells(1, 1), _
         Address:=strString
       End If

This workbook.....

       Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        If Target.Parent.Value = "+" Then Rows(Target.Parent.Row + 1).Insert
       End Sub

Answer 1

Hyperlinks(1).Parent as a Range
Hyperlinks(1).Parent.Parent as a Worksheet

So your code should be :

With ActiveSheet
    .Cells(1, 1).Value = "+"
    '''No need to test as you fill it yourself
    .Hyperlinks.Add Anchor:=.Cells(1, 1), Address:=strString
End If

Sheet's module :

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Parent.Value = "+" Then _
        Target.Parent.Parent.Rows(Target.Parent.Row + 1).Insert CopyOrigin:=xlFormatFromRightOrBelow
End Sub

or shorter :

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Parent.Value = "+" Then _
        Me.Rows(Target.Parent.Row + 1).Insert CopyOrigin:=xlFormatFromRightOrBelow
End Sub

---

I've added CopyOrigin parameter, to copy the format from the below row to avoid the reproduce the hyperlink style on the newly inserted row!

CopyOrigin takes either of one parameter as given below:

Const xlFormatFromLeftOrAbove = 0
Member of Excel.XlInsertFormatOrigin

and

Const xlFormatFromRightOrBelow = 1
Member of Excel.XlInsertFormatOrigin