利用工作表事件制作工作表目录

利用工作表事件制作工作表目录

在Sheet1的工作表标签上右键---查看代码--然后输入如下代码:

Private Sub Worksheet_Activate() '工作表激活事件
    Dim Sht As Worksheet
    Dim i As Integer
    Dim LastRow As Integer
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    i = 2
    If LastRow > 1 Then Range("A2:A" & LastRow).ClearContents
    For Each Sht In Worksheets
        If Sht.CodeName <> "Sheet1" Then
            Cells(i, 1).Value = Sht.Name
            i = i + 1
        End If
    Next
    Set Sht = Nothing
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) '单元格选择事件
    Dim LastRow As Integer
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    On Error Resume Next
    If Not Application.Intersect(Target, Range("A2:A" & LastRow)) Is Nothing Then
        Sheets(Target.Text).Select
    End If
End Sub
激活Sheet1时会自动生成目录
Sheet1的A2时,会自动跳转到单元格内容(工作表名字)的A1单元格(相当于点击超链接的效果)