next文章目录跳转_神奇的Excel VBA系列之：制作工作表目录

最新推荐文章于 2024-04-14 18:57:18 发布

weixin_39844426

最新推荐文章于 2024-04-14 18:57:18 发布

阅读量163

点赞数

文章标签： next文章目录跳转

本文链接：https://blog.csdn.net/weixin_39844426/article/details/111683049

版权

在前一篇文章“Excel插件--Power Click功能介绍08：表格目录”中，我们介绍了《Power Click》插件的一键生成工作表目录的功能。

其实如果您会使用Excel VBA表格编程。您也可以给自己定制一个更适合自己的工作表目录。本篇文章将分享VBA代码制作工作表目录的示范代码。

示例功能要求：在当前工作簿中新建工作表，名称命名为“My 目录”。然后将工作簿中的所有其它工作表名称放入“My 目录”工作表单元格区域中，并建立超链接，这样我们可以点击链接快速跳转至其它工作表中。

根据要求，我们将该功能分解为3个部。

第1步：创建变量

Sub 制作工作表目录()
'创建变量
Dim ws As Worksheet
Dim sh As Worksheet
Dim n As Integer
... ... ...
End Sub

第2步：新建“My目录”工作表，并设置表头。

Sub 制作工作表目录()
'创建变量
Dim ws As Worksheet
Dim sh As Worksheet
Dim n As Integer

'新建“My目录”工作表，并设置表头
On Error Resume Next                            '如遇错误继续运行
Application.DisplayAlerts = False               '禁用显示警告提示
Worksheets("My目录").Delete                     '删除原目录工作表
Set ws = Worksheets.Add(before:=Sheets(1))      '新建工作表
Application.DisplayAlerts = True                '恢复显示警告提示
ws.Name = "My目录"                              '新建工作表命名为目录
ws.Cells(1, "A") = "工作表目录"                 '设置目录表头名称
... ... ...
End Sub

该步骤需要注意的是，如果当前工作簿中已经存在了名称为“My目录”工作表，那么新建同名工作表将不被允许，如果不存在该工作表，那么引用该工作做也会产生运行时错误。因此我们需要先判断是否存在再决定是直接新建还是先删后建。本文中我们使用了On Error Resume Next 语句，通过该错误处理语句巧妙地忽略运行时错误，快速新建“My目录”工作表。除此方法外，我们还可以通过循环遍历或者字典的应用等等来判断当前工作簿中是否存在指定名称的工作表。如果您不理解 On Error Resume Next语句可以安装《神奇的VBA》插件了解学习。

第3步：通过循环遍历，将其它工作表名称放入“My 目录”工作表的A列区域中。并加入超链接。

Sub 制作工作表目录()
'步骤1：创建变量
Dim ws As Worksheet
Dim sh As Worksheet
Dim n As Integer

'步骤2：新建“My目录”工作表，并设置表头
On Error Resume Next                                '如遇错误继续运行
Application.DisplayAlerts = False               '禁用显示警告提示
Worksheets("My目录").Delete                     '删除原目录工作表
Set ws = Worksheets.Add(before:=Sheets(1))      '新建工作表
Application.DisplayAlerts = True                '恢复显示警告提示
ws.Name = "My目录"                                  '新建工作表命名为目录
ws.Cells(1, "A") = "工作表目录"                   '设置目录表名称

'步骤3：通过循环遍历，将其它工作表名称放入“My 目录”工作表的A列区域中。并加入超链接。
n = 2
For Each sh In Sheets                           '遍历所有工作表
    If sh.Visible <> 0 And sh.Name <> "My目录" Then '判断工作表是否隐藏或者深度隐藏
        'My目录工作表中建立跳转目录
        ws.Hyperlinks.Add Anchor:=ws.Cells(n, "A"), Address:="", _
                          SubAddress:="'" & sh.Name & "'" & "!A1", _
                          TextToDisplay:=sh.Name
        n = n + 1
    End If
Next
Set ws =Nothing
End Sub

该步骤通过循环遍历将工作表名称写入“My 目录”工作表单元格区域中，并在单元格找中锚定加入跳转链接。这里使用了Worksheet.Hyperlinks.Add方法。

Worksheet.Hyperlinks.Add (object Anchor, string Address, [object SubAddress], [object ScreenTip], [object TextToDisplay]);

参数说明：

Anchor：必选，表示超链接定位标记。 可以为Range或Shape对象。
Address：必选，表示超链接的地址，一般指的是外部链接，如网址，邮件地址等等。
SubAddress：可选。 表示超链接的子地址。
ScreenTipObject：可选。 表示当鼠标指针停留在超链接上时所显示的屏幕提示。
TextToDisplayObject：可选。 表示要显示的超链接的文本。

好了，我们运行下看看。

实际运行非常成功！但是我们发现了一个新需求，跳转后，如何快速地再跳转回“My 目录”工作表呢？其实很简单，在原有循环遍历语句块中，再增加一个超链接返回跳转即可。见下面代码：

For Each sh In Sheets                                                   '遍历所有工作表
    If sh.Visible <> 0 And sh.Name <> "My目录" Then '判断工作表是否隐藏或者深度隐藏
        'My目录工作表中建立跳转目录
        ws.Hyperlinks.Add Anchor:=ws.Cells(n, "A"), Address:="", _
                          SubAddress:="'" & sh.Name & "'" & "!A1", _
                          TextToDisplay:=sh.Name
        '【其它工作表中建立返回目录】
        sh.Hyperlinks.Add Anchor:=sh.Range("A1"), Address:="", _
                      SubAddress:="My目录!A1", _
                      TextToDisplay:="返回目录"
        n = n + 1
    End If
Next