How to use VBA code to create Coding for creating hyperlink depending on worksheet change

How to create VBA code for creating hyperlink depending on worksheet change

Requirement:

The user is dealing with coding that creates a hyperlink based on if you enter information into a column. The problem the user is having is that if any changes that touches the column triggers the coding. The user was wondering if it is possible to modify the code only if a physical entry is made in the column that the code is initiated.

Here is the code the user is using:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sPath As String
Dim sDefaultPath As String
Dim fd As FileDialog
'Submittal Package Link
Dim R As Range
Dim cell As Range
    
Set R = Intersect(Target, Range("L:L"))
    
If Not R Is Nothing Then
    For Each cell In R
        If MsgBox("Do you wish to link to the submittal package?", vbQuestion + vbYesNo, "Link to File?") = vbNo Then
            Cells(Target.Row, "B").Value = "C"
            Exit Sub
        End If
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        sDefaultPath = "J:\Projects"
        With fd
            .AllowMultiSelect = False
            .InitialFileName = sDefaultPath
            .Title = "Select File to Link to"
            .ButtonName = "Select File"
            If .Show = True Then
                sPath = .SelectedItems(1)
            Else
                Target.Hyperlinks.Delete
                Exit Sub
            End If
        End With
        ActiveSheet.Hyperlinks.Add Anchor:=Target, Address:=sPath
        Cells(Target.Row, "B").Value = "C"
        MsgBox "Link successfully created to " & sPath, vbInformation, "Link Created"
    Next cell
End If
    
End Sub

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148225-coding-for-creating-hyperlink-depending-on-worksheet-change

Solution:

Add this immediately below your Dim statements

Code: