I'm dealing with coding that creates a hyperlink based on if you enter information into a column. The problem I'm having is that if any changes that touches the column triggers the coding. I 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.
Anyways here is the code I'm using:
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
Also if there is a better way of doing it, please let me know.