Excel利用VBA编写返回总表的快捷按钮
1、打开excel表格,可看到第一个工作表为目录的显示表
2、然后按快捷键alt+f11打开宏界面
3、然后插入模块
4、双击模块并输入如下的代码:Sub 创建返回按钮() Dim wSht As Wor氯短赤亻ksheet, B As Button, wShtname$ On Error Resume Next wShtname = "目录表" For Each wSht In Worksheets With wSht If .Name <> wShtname Then .Shapes(wShtname).Delete Set B = .Buttons.Add(0, 0, 90, 30) With B .Name = wShtname .Characters.Text = "返回目录表" .OnAction = "返回到目录表" End With End If End With Next Set B = Nothing End Sub Sub 返回到目录表() Worksheets("目录表").Activate End Sub Dim wsht As Worksheet, k%, wshtname$ Columns(1).ClearContents '清空A列数据,这样就可以随着工作表的变化,生成不同的工作表目录 Range("a1") = "超链接目录" '第一个单元格写入字符串 k = 1 '赋予参数初值 For Each wsht In Worksheets '找到当前工作簿的每个工作表 wshtname = wsht.Name If wshtname <> ActiveSheet.Name Then k = k + 1 ActiveSheet.Hyperlinks.Add anchor:=Cells(k, 1), Address:="", SubAddress:="'" & wshtname & "'!a1", TextToDisplay:=wshtname End If Next End Sub
5、回到excel界面,按快捷键alt+f8打开宏操作界面
6、找到命令按钮生成返回按钮,单击执行即可
7、看到生成的超链接,单击即可进入对应的工作表,单击返回的按钮即可返回
8、最后保存文件