September 25, 2021

Insert Hyperlink Automatically

How to insert hyperlink automatically for a large text using VBA. Watch the video below:

Insert Hyperlink Automatically with VBA for large text

Just imagine you are an avid traveler. You discover a number of lovely places to visit in a city on Google maps. Instead of wasting time, we can save the links to the various interesting locations by copying their link from Google maps into our Excel worksheet. In the Excel worksheet we write a worksheet change event macro that converts the copied link to a hyperlink. Now by clicking on this hyperlink in our worksheet we can locate our destination quickly and easily on the map. Given below is the complete VBA or macro code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myURL As String
myURL = Target.Value
If Not Intersect(Cells, Target) Is Nothing Then
Target.Hyperlinks.Add Anchor:=Target, Address:=myURL
End If
End Sub

In this manner we have automated the process of inserting a hyperlink in our worksheet.

Insert Hyperlink Automatically Using VBA
Insert Hyperlink Automatically using Google Maps data