|
hcy1185 发表于 2012-3-10 16:15
谢谢happym8888老师!
Sub 生成工作表目录()
With Sheet1
纯事件打造,打开工作薄自动自成
添加删除工作表时,需要关闭工作薄重新打开,
也可以将 刷新目录 添加到模块中,然后设置快捷键来操作,
添加到 模块1 中,
则 open 事件中的 Call 刷新目录 应改为 Call 模块1.刷新目录
- Dim Lx As Byte
- Private Sub Workbook_Open()
- Call 刷新目录
- End Sub
- Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
- With Target
- If .Count > 1 Then Exit Sub
- If .Row = 1 Then Exit Sub
- Lx = Sh.Range("IV1").End(xlToLeft).Column
- If .Row > Sh.Cells(1, Lx).End(xlDown).Row Then Exit Sub
- If Not Sh.Cells(1, Lx).Value Like "工作表目录" Then Exit Sub
- If .Column = Lx Then Sheets(.Value).Select
- End With
- End Sub
- Sub 刷新目录()
- Dim Hx As Byte
- Dim Sh As Worksheet, Sh1 As Worksheet
- For Each Sh In Worksheets
- With Sh
- Lx = .Range("Iv1").End(xlToLeft).Column + 1
- Hx = 1
- If Not .Cells(1, Lx - 1).Value Like "工作表目录" And Lx > 2 Then
- .Cells(Hx, Lx).Value = "工作表目录"
- For Each Sh1 In Worksheets
- If Sh1.Name <> Sh.Name Then
- Hx = Hx + 1
- With .Cells(Hx, Lx)
- .Value = Sh1.Name
- .Font.Underline = 2
- .Font.ColorIndex = 5
- .HorizontalAlignment = 3
- End With
- End If
- Next
- End If
- End With
- Next
- End Sub
复制代码
|
|