How to create VBA code for creating hyperlink depending on worksheet change
Category: [Excel] Demo Available
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
Solution:
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.
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to change reference columns in another worksheet using VBA looping |
| How to auto populate comment box when cell value changes |
| How to hide columns in sheet 2 based on data change in sheet 1 |
| How to use VBA to change zero value to blank value based on criteria in other columns |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.