Excel VBA添加超链接到形状以链接到另一个工作表 [英] Excel VBA add hyperlink to shape to link to another sheet

问题描述

我有一个宏在工作簿的前面创建一个摘要表。在工作簿中的工作表之后创建和标记形状，然后将超链接添加到形状以重定向到这些工作表，但是，当我记录宏来执行此操作时，生成的代码是：

I have a macro that creates a summary sheet at the front of a Workbook. Shapes are created and labeled after the sheets in the workbook and then hyperlinks are added to the shapes to redirect to those sheets, however, when I recorded the macro to do this, the code generated was:

ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:=""

在录制宏工作时手动创建的超链接当它们悬停在它们上面时，显示文件路径和 - Sheet！A1，但是似乎并没有将链接位置添加到宏的地址部分。有没有人知道应该在该地址部分链接到表格的代码？

The hyperlinks that were manually created in excel while recording the macro work just fine and when hovering over them, display the file path and " - Sheet!A1" but they don't seem to actually be adding the link location into the address portion of the macro. Does anyone know the code that should go in that address section to link to the sheet?

推荐答案

宏记录器不记录在这种情况下实际发生了什么。您正在寻找的属性是 SubAddress 。

The macro recorder doesn't record what is actually happening in this case. The property you are looking for is SubAddress. Address is correctly set in your code.

从形状创建超链接，而不选择 strong>

如果可能，你想避免在代码中选择东西，在这种情况下它肯定是。创建一个形状变量并将其设置为要修改的形状，然后将超链接添加到形状所在的工作表上。请注意，您还可以设置屏幕提示的文本。

Create a hyperlink from a shape without selecting it
You want to avoid selecting things in your code if possible, and in this case it definitely is. Create a shape variable and set it to the shape you want to modify, then add the hyperlink to the sheet the shape is on. Note that you can also set the text for the screen tip.

在下面的示例中，我要修改的形状在第6页，超链接到表4。

In the example below, the shape I want to modify is on Sheet 6, and hyperlinks to a range on Sheet 4.

Sub SetHyperlinkOnShape()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet6")

    Dim hyperLinkedShape As Shape

    Set hyperLinkedShape = ws.Shapes("Rectangle 1")

    ws.Hyperlinks.Add Anchor:=hyperLinkedShape, Address:="", _
        SubAddress:="Sheet4!C4:C8", ScreenTip:="yadda yadda"
End Sub

这篇关于Excel VBA添加超链接到形状以链接到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！