Option Explicit
'功能:创建【目录导航】工作表
'作者:E精精
'----------------------------------------------
Sub 创建目录工作表()
Dim ct_sht As Worksheet
On Error Resume Next
Set ct_sht = Worksheets('目录导航')
'存在就清空,不存在创建一个
If Err.Number = 0 Then
ct_sht.Cells.Clear
Else
ThisWorkbook.Worksheets.Add Before:=Worksheets(1)
ActiveSheet.Name = '目录导航'
End If
End Sub
'功能: 生成带超链接的工作表目录
'作者:E精精
'----------------------------------------------
Sub 超链接目录()
Dim sht As Worksheet, n As Long
With Sheets('目录导航')
'标题
.[A1:b1] = Array('序号', '工作表名称')
.Columns(1).HorizontalAlignment = XlHAlign.xlHAlignCenter
'循环添加到目录导航表中
For Each sht In ThisWorkbook.Worksheets
If sht.Name <> '目录导航' Then
n = n + 1
.Cells(n + 1, 1) = n
'添加超链接
.Hyperlinks.Add .Cells(n + 1, 2), '', _
''' & sht.Name & ''!A1', , sht.Name
.Cells(n + 1, 2).Font.Underline = False
End If
Next
End With
End Sub
'功能: 每个表中返回目录功能
Option Explicit
'功能:创建【目录导航】工作表
'作者:E精精
'----------------------------------------------
Sub 创建目录工作表()
Dim ct_sht As Worksheet
On Error Resume Next
Set ct_sht = Worksheets('目录导航')
'存在就清空,不存在创建一个
If Err.Number = 0 Then
ct_sht.Cells.Clear
Else
ThisWorkbook.Worksheets.Add Before:=Worksheets(1)
ActiveSheet.Name = '目录导航'
End If
End Sub
'功能: 生成带超链接的工作表目录
'作者:E精精
'----------------------------------------------
Sub 超链接目录()
Dim sht As Worksheet, n As Long
With Sheets('目录导航')
'标题
.[A1:b1] = Array('序号', '工作表名称')
.Columns(1).HorizontalAlignment = XlHAlign.xlHAlignCenter
'循环添加到目录导航表中
For Each sht In ThisWorkbook.Worksheets
If sht.Name <> '目录导航' Then
n = n + 1
.Cells(n + 1, 1) = n
'添加超链接
.Hyperlinks.Add .Cells(n + 1, 2), '', _
''' & sht.Name & ''!A1', , sht.Name
.Cells(n + 1, 2).Font.Underline = False
End If
Next
End With
End Sub
'功能: 每个表中返回目录功能
'作者:E精精
'----------------------------------------------
Sub 返回目录()
Dim shp As Shape, sht As Worksheet
For Each sht In Sheets
If sht.Name <> '目录导航' Then
'删除原有的
For Each shp In sht.Shapes
If shp.Name = sht.Name Then
shp.Delete
End If
Next
'重新添加
Set shp = sht.Shapes.AddShape(msoShapeRectangle, 15, 15, 80, 20)
sht.Activate
With shp
.Name = sht.Name
.Select
.TextFrame2.TextRange.Text = '返回目录'
End With
sht.Hyperlinks.Add Selection.ShapeRange.Item(1), '', '目录导航!A1'
End If
Next
Sheets('目录导航').Activate
End Sub
Sub 一键目录()
'调用生成 目录导航工作表功能
Call 创建目录工作表
'调用生成目录超链接
Call 超链接目录
'添加返回目录功能
Call 返回目录
End Sub
联系客服