这是一个
远的
比…更复杂的方法
Adreas'
但并不具有同样的风险,轻度的技术娴熟的用户只需右键单击文件并弹出到“属性”面板。
创建两个新工作表。其中一个设置为
xlVeryHidden
-下面的例子我称之为
hsSheetStatus
. 另一个,有一个很好的大通知告诉你
倒霉的奴才
同事启用宏时,将更改可见性。我叫这个
hsEnableNotice
.
然后我有一个宏要显示
启用通知
隐藏所有其他工作表(将其可见性和活动工作表存储在
hs工作表状态
)和第二个要执行反向操作的宏(从中恢复可见性/活动表)
hs工作表状态
)然后让它们继续运行
Workbook_BeforeSave
,
Workbook_AfterSave
和
Workbook_Open
:
Option Explicit
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
UnlockAndShow
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
LockAndHide
End Sub
Private Sub Workbook_Open()
UnlockAndShow
End Sub
Private Sub LockAndHide()
Dim lSheet As Long, ActiveName As String
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveName = ThisWorkbook.ActiveSheet.Name
hsEnableNotice.Visible = xlSheetVisible
hsEnableNotice.Activate
hsSheetStatus.Range(hsSheetStatus.Cells(1, 1), hsSheetStatus.Cells(hsSheetStatus.Rows.Count, 1)).EntireRow.Delete
For lSheet = 1 To ThisWorkbook.Sheets.Count 'By using Sheets instead of Worksheets, we include Charts etc
hsSheetStatus.Cells(lSheet, 1).Value = ThisWorkbook.Sheets(lSheet).Name
hsSheetStatus.Cells(lSheet, 2).Value = ThisWorkbook.Sheets(lSheet).Visible
If ThisWorkbook.Sheets(lSheet).Name = ActiveName Then hsSheetStatus.Cells(lSheet, 3).Value = 1
If ThisWorkbook.Sheets(lSheet).Name <> hsEnableNotice.Name Then ThisWorkbook.Sheets(lSheet).Visible = xlSheetVeryHidden
Next lSheet
ThisWorkbook.Protect Password:="ThisIsMyPassword.ThereAreManyLikeIt,ButThisOneIsMine.", Structure:=True, Windows:=False
Application.EnableEvents = True
End Sub
Private Sub UnlockAndShow()
Dim WasSaved As Boolean, lSheet As Long, lMax As Long
WasSaved = ThisWorkbook.Saved
Application.ScreenUpdating = False
Application.EnableEvents = False
lMax = hsSheetStatus.Cells(hsSheetStatus.Rows.Count, 1).End(xlUp).Row
ThisWorkbook.Unprotect Password:="ThisIsMyPassword.ThereAreManyLikeIt,ButThisOneIsMine."
For lSheet = 1 To lMax
ThisWorkbook.Sheets(hsSheetStatus.Cells(lSheet, 1).Value).Visible = hsSheetStatus.Cells(lSheet, 2).Value
If hsSheetStatus.Cells(lSheet, 3).Value = 1 Then ThisWorkbook.Sheets(hsSheetStatus.Cells(lSheet, 1).Value).Activate
Next lSheet
hsSheetStatus.Visible = xlSheetVeryHidden
hsEnableNotice.Visible = xlSheetVeryHidden
Application.EnableEvents = True
Application.ScreenUpdating = True
ThisWorkbook.Saved = WasSaved
End Sub