Automatic Sheet Hyperlinks Based on Name

[szymagic]

Hello,

I'd like to be able to have excel create a hyperlink to a sheet whenever the sheet name is typed out in a specific range on a "home page"

I currently have this written in a worksheet change event, but I'm fearful that this will severely bog down my spreadsheet once more sheets are created.

Here is my code so far:

Private Sub Worksheet_Change(ByVal Target As Range)Dim ws As Worksheet
Dim rng As Range: Set rng = Range("A1:A10")
Dim cel As Range
For Each cel In rng.Cells
    For Each ws In ThisWorkbook.Worksheets
        If cel.Value = ws.Name Then
            Application.ActiveSheet.Hyperlinks.Add Anchor:=Cells(cel.Row, cel.Column), Address:="", SubAddress:= _
            "'" & ws.Name & "'!R1C1", TextToDisplay:=ws.Name
        End If
    Next ws
Next cel
End Sub

Is there a way to specify for this code to run only when a change within the defined range is executed, instead of the entire worksheet?

Thank you for your help!

 

[Scott T]

See this you can use intersect to see if Target is in a certain range.
https://www.ozgrid.com/VBA/vba-intersect.htm

 

[szymagic]

Thanks Scott,

That did the trick! Should've been able to find that on my own... sorry