作者:乔山办公网日期:
返回目录:excel表格制作
做了个Demo给楼主,Demo中隐藏了Sheet3当超链e799bee5baa6e79fa5e98193e59b9ee7ad94331接链接到Sheet3时,显示Sheet3并实现超链接跳转。当离开Sheet3时,Sheet3自动隐藏:
插入模块并定义一个全局变量:
Public shtVisible As Variant
在WorkBook中插入代码:
Private Sub Workbook_Open()
ThisWorkbook.Sheets(3).Visible = False
Dim i!
ReDim shtVisible(Sheets.Count)
For i = 1 To Sheets.Count
shtVisible(i) = Sheets(i).Visible
Next
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim i
For i = 1 To Sheets.Count
If Sheets(i).Name = Sh.Name Then Sh.Visible = shtVisible(i)
Next
End Sub
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
If InStr(Target.SubAddress, "!") > 0 Then
Dim sht As Object
Set sht = Sheets(Left(Target.SubAddress, InStr(Target.SubAddress, "!") - 1))
Application.EnableEvents = False
sht.Visible = True
Target.Follow
Application.EnableEvents = True
End If
End Sub
附件请下载参考
可以变通一下百也可以达到度这样的效果
Excel 超链知接打不开隐藏道的工作表.-Excel基础应用内-ExcelHome技术论坛 - http://club.excelhome.net/thread-318698-1-1.html
参考网址容
Private Sub Workbook_Open()
For i = 1 To Sheets.Count
If Sheet(i).Name <> "目录抄袭" Then Sheets(i).Visible = 2
Next
End Sub
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
t = Left(Target.Range, InStr(Target.Range, "!zhidao") - 1)
Sheets(t).Visible = -1
Sheets(t).Select
End Sub
这是因为 Excel 中超链接对隐藏工作表失效,需要做超链接就不能来隐藏工作表。
如果 sheet1 是主表自,后面有 n 多个 sheet,在 sheet1 上建立了后面所有 sheet 的超级链接,然后把后百面所有的 sheet 都隐藏,超链接就失效了。这时,可以在度主表里添加上复选框,选上就执知行代码如 Sheet2.select,再在 Sheet2 里制作个按钮,点击就返回主道表,或者直接在 sheet2 的 Deactivate 事件中写入
Sheet1.select
sheet2.visible=false
代码的意思是当你的焦点离开 Sheet2 时,就返回主表,并隐藏 Sheet2。