工作表目录超链接及返回

Private Sub Workbook_Open()

'提取各个工作表标签名称并在第一页建立目录和超级链接的程序加返回程序

Dim mysheet As Worksheet

Dim Rowindex_1, Columndex_1, StartRowindex As Integer

Dim j As String

Rowindex_1 = 2 ''''定义菜单起始行数

Columndex_1 = 2 ''''定义菜单所在列

StartRowindex = Rowindex_1

Sheets("目录").Select

Columns(Columndex_1).ClearContents

Columns(Columndex_1).NumberFormatLocal = "@"

For Each mysheet In Worksheets

If Rowindex_1 = StartRowindex Then

Cells(Rowindex_1 - 1, Columndex_1) = "菜单"

Else

Cells(Rowindex_1 - 1, Columndex_1).Select

Selection = mysheet.Name

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=mysheet.Name & "!a1", TextToDisplay:=mysheet.Name '建立超级链接

Sheets(mysheet.Name).Select

ActiveSheet.Shapes.AddTextEffect(msoTextEffect1, "返回", "宋体", 16#, msoTrue, _

msoFalse, 14311, 4).Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:= _

"", SubAddress:="目录!A1"

Sheets("目录").Select

End If

Rowindex_1 = Rowindex_1 + 1

Next mysheet

End Sub