Excel VBA: how to auto-create a hyperlink from a cell value?
I have a table called Table1
In column B, I have the ticket number. for example: 76537434
Requirement: When any change occurs in any cell in column B, that cell (Target cell) must be replaced with a hyperlink so that the hyperlink address will be example.com/id=76537434
Cell value i.e. 76537434 must remain the same
+3
source to share
3 answers
Add this event handler to your worksheet code module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
Target.Hyperlinks.Delete ' or Target.ClearHyperlinks to conserve the formatting
Me.Hyperlinks.Add Target, "http://example.com/id=" & Target.value
End Sub
+3
source to share
The following event Worksheet_Change
should solve your problem:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim tmp As String
If Intersect(Range("B:B"), Target) Is Nothing Then Exit Sub
For Each cell In Target
If cell.Column = 2 Then
Application.EnableEvents = False
tmp = cell.Value2
cell.Parent.Hyperlinks.Add _
Anchor:=Cells(cell.Row, 2), _
Address:="http://example.com/id=" & tmp, _
TextToDisplay:=tmp
Application.EnableEvents = True
End If
Next cell
End Sub
Please note that you must copy it to a sheet and not to a separate module.
+2
source to share
=HYPERLINK(E14&F14,"Name")
where cell E14 contains " http://www.example.com/id= " and cell F14 contains "76537434". These souls don't need VBA macros.
0
source to share