I have a table called Table1
我有一个名为Table1的表
In Column B, I have the ticket number. e.g: 76537434
在B栏中,我有票号。例如:76537434
Requirement: when any change happens in any cell in column B, that cell (Target cell) to be changed into a hyperlink such that the hyperlink address would be example.com/id=76537434
要求:当B列中的任何单元格发生任何更改时,该单元格(目标单元格)将更改为超链接,以便超链接地址为example.com/id=76537434
Cell value i.e. 76537434 must remain the same
单元格值即76537434必须保持不变
3 个解决方案
#1
3
Add this event handler to your worksheet's 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
#2
2
The following Worksheet_Change
event should be able to solve your problem:
以下Worksheet_Change事件应该能够解决您的问题:
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
Note, that you must copy it to the sheet and not into a separate module.
请注意,您必须将其复制到工作表而不是单独的模块。
#3
0
=HYPERLINK(E14&F14,"Name")
where cell E14 contains "http://www.example.com/id=" and cell F14 contains "76537434". This soultions doesn't need VBA macros.
其中,单元格E14包含“http://www.example.com/id=”,单元格F14包含“76537434”。这个灵魂不需要VBA宏。
#1
3
Add this event handler to your worksheet's 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
#2
2
The following Worksheet_Change
event should be able to solve your problem:
以下Worksheet_Change事件应该能够解决您的问题:
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
Note, that you must copy it to the sheet and not into a separate module.
请注意,您必须将其复制到工作表而不是单独的模块。
#3
0
=HYPERLINK(E14&F14,"Name")
where cell E14 contains "http://www.example.com/id=" and cell F14 contains "76537434". This soultions doesn't need VBA macros.
其中,单元格E14包含“http://www.example.com/id=”,单元格F14包含“76537434”。这个灵魂不需要VBA宏。