Excel VBA 在不同工作表中创建指向同一工作簿中不同工作表的超链接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20106798/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Creating a Hyperlink in a different worksheet to a different worksheet in the same workbook
提问by The Silent Cartographer
I am working on a project in Excel where I want to validate a data set, whenever two cells in a column have a duplicate I want to write the location of the duplicate to a different worksheet that contains a bunch of information regarding errors in the data.
我正在 Excel 中处理一个项目，我想在其中验证数据集，每当列中的两个单元格有重复项时，我想将重复项的位置写入另一个工作表，其中包含一堆有关数据错误的信息.
So my data is in worksheet1 and when I run my code I am writing a hyperlink to some cell in worksheet2 that links to the error in worksheet1.
所以我的数据在 worksheet1 中，当我运行我的代码时，我正在写一个指向 worksheet2 中某个单元格的超链接，该链接链接到 worksheet1 中的错误。
I found some code which almost accomplishes this task in a different stack question...
我在不同的堆栈问题中发现了一些几乎可以完成此任务的代码...
Hyperlink to Existing Worksheet in actual Workbook
ActiveSheet.Hyperlinks.Add ActiveCell, "", Sheets(fortnr).Name & "!A1"
However this creates a hyper link in the workbook you are currently in (or is active).
但是，这会在您当前所在（或处于活动状态）的工作簿中创建一个超链接。
I modified the code a bit, but can't seem to get it to run...
我稍微修改了代码，但似乎无法让它运行......
Sheets("Sheet3").Hyperlinks.Add Cells(3, 3), "", Sheets("Sheet1").Name & "!B2", _
"", "Hello"
I have also tried
我也试过
Sheets("Sheet3").Activate
ActiveSheet.Hyperlinks.Add Cells(3, 3), "", Sheets(fortnr).Name & "!A1"
Sheets("Sheet1").Activate
But it doesn't work. There is no error produced either so I am really at a loss as to why this isn't working.
但它不起作用。也没有产生错误，所以我真的不知道为什么这不起作用。
采纳答案by Portland Runner
Your anchor cell isn't specifying the sheet to anchor to so it defaults to the active sheet.
您的锚定单元格没有指定要锚定的工作表，因此它默认为活动工作表。
Change it like this:
像这样改变它：
Sheets("Sheet3").Hyperlinks.Add Sheets("Sheet3").Cells(3, 3), "", Sheets("Sheet1").Name & "!B2", "", "Hello"
Why do you need to have a sheet before .Hyperlinks?
为什么你之前需要有一张表.Hyperlinks？
Based on the MSDN documentation hereI believe it's because HyperLinksis a method of the sheetsclass so it's the only way to invoke the method. You can use Activesheetor call any sheet at the start to invoke the method then assign the hyperlink anchor to your desired location.
基于此处的 MSDN 文档，我相信这是因为它HyperLinks是sheets类的方法，因此它是调用该方法的唯一方法。您可以Activesheet在开始时使用或调用任何工作表来调用该方法，然后将超链接锚点分配到您想要的位置。
回答by user5293686
These are the ones I use to create hyperlinks on VBA to a URL or a specific spreadsheet
这些是我用来在 VBA 上创建指向 URL 或特定电子表格的超链接的那些
Sheets("RogueOne").Hyperlinks.Add Anchor:=Sheets("RogueOne").Cells(3, 4), Address:="http://wired.com", ScreenTip:="Wired", TextToDisplay:="WIRED"
Sheets("RogueOne").Hyperlinks.Add Sheets("RogueOne").Cells(5, 5), "", Sheets("RogueTwo").Name & "!c2", "", "RogueTwo"
回答by NewLearner
Perhaps you could try this...
也许你可以试试这个......
Dim WS as Worksheet
ActiveSheet.Hyperlinks.add Anchor:=Selection, Address:="", SubAddress:= _
"'" & WS.Name & "'" & "!A1", TextToDisplay:=WS.Name
Because when you look at the link, it usually shows
因为当你查看链接时，它通常会显示
file:///name'WS.name'!A1
and it could be possible that the pair of ' ' may have been omitted.
并且可能省略了“ ”对。
回答by Michal Knytl
In my case I experienced "Hyperlink is not valid" error when I clicked to hyperlink created by Portlands Runner code. Surrounding and joining Sheets("Sheet1").Nameby aphostrophes resolved that problem and that error was gone. If you experience the same, then use it like this:
就我而言，当我单击 Portlands Runner 代码创建的超链接时，我遇到了“超链接无效”错误。环绕并加入Sheets("Sheet1").Nameby aphostrophes 解决了该问题，该错误消失了。如果您遇到相同的情况，请像这样使用它：
Sheets("Sheet3").Hyperlinks.Add Sheets("Sheet3").Cells(3, 3), "", "'" & Sheets("Sheet1").Name & "'" & "!B2", "", "Hello"
