Excel VBA链接两个用户选择的单元格与超链接

Question

我目前正试图用超链接自动链接两个用户选定的单元格。 用户可以选择两个不同的单元格（通过输入框一个接一个地）。为了这两个细胞超链接应增加从小区1指向小区2，小区2单元1

到目前为止，我有这样的：

Option Explicit 

Sub Hyperlinks() 
Dim FirstHyperlink As Range 
Dim SecondHyperlink As Range 

Set FirstHyperlink = Application.InputBox("Please select first cell to contain hyperlink", "Hyperlink 1 selection", Type:=8) 
Set SecondHyperlink = Application.InputBox("Please select second cell to contain hyperlink", "Hyperlink 2 selection", Type:=8) 

ActiveSheet.Hyperlinks.Add Anchor:=FirstHyperlink.Address, Address:="", SubAddress:= _ 
    SecondHyperlink.Address, TextToDisplay:=FirstHyperlink.Value 

ActiveSheet.Hyperlinks.Add Anchor:=SecondHyperlink.Address, Address:="", SubAddress:= _ 
    FirstHyperlink.Address, TextToDisplay:=SecondHyperlink.Value 

End Sub 

但是我得到一个运行时错误13：类型不匹配指向

ActiveSheet.Hyperlinks.Add Anchor:=FirstHyperlink.Address, Address:="", SubAddress:= _ 
     SecondHyperlink.Address, TextToDisplay:=FirstHyperlink.Value 

任何想法是什么原因造成的问题？

Answer 1

只是解决您的Anchors：

Option Explicit 

Sub Hyperlinks() 
Dim FirstHyperlink As Range 
Dim SecondHyperlink As Range 

Set FirstHyperlink = Application.InputBox("Please select first cell to contain hyperlink", "Hyperlink 1 selection", Type:=8) 
Set SecondHyperlink = Application.InputBox("Please select second cell to contain hyperlink", "Hyperlink 2 selection", Type:=8) 

ActiveSheet.Hyperlinks.Add Anchor:=FirstHyperlink, Address:="", SubAddress:= _ 
    SecondHyperlink.Address, TextToDisplay:=FirstHyperlink.Value 

ActiveSheet.Hyperlinks.Add Anchor:=SecondHyperlink, Address:="", SubAddress:= _ 
    FirstHyperlink.Address, TextToDisplay:=SecondHyperlink.Value 

End Sub 

这假定细胞具有一些非空值开始。

Answer 2

出于完整性原因：

这里是我最后一起工作的代码。我写了一些更多的代码行，使它可以在不同的工作表上工作，而不仅仅在一张工作表上工作。

Option Explicit 

Sub Hyperlinks() 
Dim FirstHyperlink As Range 
Dim SecondHyperlink As Range 
Dim FirstSheet As Worksheet 
Dim SecondSheet As Worksheet 

Set FirstHyperlink = Application.InputBox("Please select first cell to contain hyperlink", "Hyperlink 1 selection", Type:=8) 
Set FirstSheet = FirstHyperlink.Worksheet 
Set SecondHyperlink = Application.InputBox("Please select second cell to contain hyperlink", "Hyperlink 2 selection", Type:=8) 
Set SecondSheet = SecondHyperlink.Worksheet 

ActiveSheet.Hyperlinks.Add Anchor:=FirstHyperlink, Address:="", SubAddress:= _ 
    "'" & SecondSheet.Name & "'" & "!" & SecondHyperlink.Address, TextToDisplay:=FirstHyperlink.Value 

ActiveSheet.Hyperlinks.Add Anchor:=SecondHyperlink, Address:="", SubAddress:= _ 
    "'" & FirstSheet.Name & "'" & "!" & FirstHyperlink.Address, TextToDisplay:=SecondHyperlink.Value 

End Sub