Coding for creating hyperlink depending on worksheet change.

zoog25 · Apr 27th 2017

Hello all,

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:

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

Display More

Also if there is a better way of doing it, please let me know.

Logit · Apr 28th 2017

Re: Coding for creating hyperlink depending on worksheet change.

I ran your macro here. I'm not experiencing the same issue. Seems to work fine.

I can enter anything, anywhere on the sheet EXCEPT Col L and nothing happens. If I enter something anywhere in Col L, the macro fires.

Did I misunderstand your description of the issue ?

zoog25 · Apr 28th 2017

Re: Coding for creating hyperlink depending on worksheet change.

If you use do something like insert a row then the code runs or gives me an error

**KjBox** · Apr 28th 2017

Re: Coding for creating hyperlink depending on worksheet change.

Add this immediately below your Dim statements

Code

If Target.Count > 1 Then Exit Sub

Then if more than one cell is changed, such as inserting a row or column, then the macro will fire but immediately exit without changing anything.

zoog25 · Apr 28th 2017

Re: Coding for creating hyperlink depending on worksheet change.

Thanks KjBox. The addition of the code work perfectly.

Coding for creating hyperlink depending on worksheet change.

Participate now!

Share