天瑞科技 - 深圳电脑维修，深圳电脑维护，深圳电脑修理， 上门维修电脑，深圳电脑上门维修

天瑞电脑公司　　正在为您转接...

目标：在EXCEL工作薄中的INDEX工作表中出现其他所有工作表的名称和链接。相当于在EXCEL中做“目录”。
放置位置：“工具”菜单下“宏”－＞“Visaul Basic编辑器”。在当前工作薄中“Thisworkbook"中粘入以下代码。
代码：

Sub writeSheetName()
Dim I, writeI As Integer
Dim SheetName As String
writeI = 4

Cells.Select
Selection.Delete Shift:=xlUp ’删除据有的单元格数据
Worksheets("INDEX").Cells(2, 2) = "目录与索引" ’写上标题
Cells(1, 1).Select

For I = 1 To Application.ActiveWorkbook.Worksheets.Count
SheetName = Application.ActiveWorkbook.Worksheets(I).Name
If SheetName <> "Index" Then
Worksheets("INDEX").Cells(writeI, 2) = SheetName
ActiveSheet.Hyperlinks.Add Anchor:=Cells(writeI, 2), Address:="", SubAddress:=SheetName + "!A1", TextToDisplay:=SheetName
writeI = writeI + 2
End If
Next I
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name = "Index" Then
Call writeSheetName
End If
End Sub


注意：工作表Index的大小写形式