Excel VBA:如何从单元格值自动创建超链接?

时间:2021-11-26 02:29:18

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宏。