Option Explicit
Option Compare Text
Dim ws As Worksheet
Const MaxUses As Long = 5 '<- change uses
Const wsWarningSheet As String = "Splash"
Private Type mySheetVisibilityStructure
sSheetName As String
iVisibility As Long
End Type
Const sSheetNameThatMUST_REMAIN_VISIBLE = "Splash"
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wks As Worksheet
Dim mySheetVisibilityStructureArray() As mySheetVisibilityStructure
Dim i As Long
Dim iVisibility As Long
Dim iVisibilityErrorSheet As Long
Dim sActiveSheetName As String
Dim sErrorSheetName As String
Dim sSheetName As String
'Initialize the 'Sheet Visibiilty Structure Array'
ReDim mySheetVisibilityStructureArray(1 To 1)
'Save the 'Active Sheet' Name
sActiveSheetName = ActiveSheet.Name
'Verify that the 'Master Sheet' exists
On Error Resume Next
iVisibility = Sheets(sSheetNameThatMUST_REMAIN_VISIBLE).Visible
If Err.Number <> 0 Then
Err.Clear
MsgBox "SAVE NOT DONE. Data Integrity Error." & vbCrLf & _
"In order to save this file Sheet '" & sSheetNameThatMUST_REMAIN_VISIBLE & "' MUST EXIST." & vbCrLf & vbCrLf & _
"WARNING. If this condition is NOT CORRECTED, Data may be LOST."
Cancel = True 'Cancel Save
On Error GoTo 0
Exit Sub
End If
On Error GoTo 0
'Disable 'Screen Updating' to eliminate Screen Flicker
Application.ScreenUpdating = False
'Save the 'Visibility of Each Sheet'
'Make all Sheets Hidden Except the 'Master Sheet'
For Each wks In ThisWorkbook.Sheets
'Add an element to the 'Sheet Visibiilty Structure Array'
'Put the 'Sheet Name' and the 'Sheet Visibility' in the Array
i = i + 1
ReDim Preserve mySheetVisibilityStructureArray(1 To i)
mySheetVisibilityStructureArray(i).sSheetName = wks.Name
mySheetVisibilityStructureArray(i).iVisibility = wks.Visible
'Make the 'Master Sheet' visible and the Active Sheet
'Hide All other Sheets
If UCase(wks.Name) = UCase(sSheetNameThatMUST_REMAIN_VISIBLE) Then
'Make the 'Master Sheet' visible and make the 'Master Sheet' the 'Active Sheet'
wks.Visible = xlSheetVisible
wks.Activate
Else
'Hide all other Sheets
wks.Visible = xlSheetVeryHidden 'Can be 'xlSheetHidden' or 'xlSheetVeryHidden'
End If
Next wks
'Turn Off Excel Events
Application.EnableEvents = False
'Cancel Save - to prevent recursion
Cancel = True
'Save this file
ThisWorkbook.Save
'Restore Original Sheet Visibility
For i = LBound(mySheetVisibilityStructureArray) To UBound(mySheetVisibilityStructureArray)
sSheetName = mySheetVisibilityStructureArray(i).sSheetName
iVisibility = mySheetVisibilityStructureArray(i).iVisibility
'A runtime error will occur if Excel attempt to hide all Sheets
On Error Resume Next
Sheets(sSheetName).Visible = iVisibility
If Err.Number = 1004 Then
Err.Clear
sErrorSheetName = sSheetName
iVisibilityErrorSheet = iVisibility
End If
On Error GoTo 0
Next i
'If a Sheet had a runtime error - restore it's original visibility
If Len(sErrorSheetName) > 0 Then
Sheets(sErrorSheetName).Visible = iVisibilityErrorSheet
End If
'Resume with the 'Original Active Sheet'
Sheets(sActiveSheetName).Activate
'Turn On Excel Events
'Turn On Screen Updating
Application.EnableEvents = True
Application.ScreenUpdating = True
'Reset Iterations in an attempt to prevent 'Circular Reference' Error
Application.Iteration = True
Application.MaxIterations = 1
Application.MaxChange = 0.001
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub
Public Sub MakeAllSheetsVisible()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Sheets
wks.Visible = xlSheetVisible
Next wks
End Sub
Private Sub Workbook_Open()
For Each ws In ThisWorkbook.Sheets
If ws.Name = wsWarningSheet Then
ws.Visible = True
Else
ws.Visible = xlVeryHidden
End If
Next
'record opening in remote cell
With Sheets(wsWarningSheet).Cells(Rows.Count, Columns.Count)
End With
Const sHide2 As String = "AA:AA, Ak:Ak, Ap:Ap, AQ:AQ, Av:Av, Aw:Aw, Bb:Bb, Bc:Bc, Bh:Bh, Bi:Bi, Bn:Bn, Bo:Bo, Bt:Bt, Bu:Bu, Bz:Bz, ca:ca "
Const sHide4 As String = "I:I, O:O"
Const sHide5 As String = "I:I, N:N"
With Sheet2
Application.EnableEvents = False
.Cells(1, 24).ClearContents
Application.EnableEvents = True
.Unprotect
'.Range(sHide2 & 1).EntireColumn.Hidden = True
.Range(sHide2).EntireColumn.Hidden = True
.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
.EnableSelection = xlUnlockedCells
End With
With Sheet4
Application.EnableEvents = False
.Cells(2, 16).ClearContents
Application.EnableEvents = True
.Unprotect
'.Range(sHide2 & 1).EntireColumn.Hidden = True
.Range(sHide2).EntireColumn.Hidden = True
.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
.EnableSelection = xlUnlockedCells
End With
With Sheet5
Application.EnableEvents = False
.Cells(1, 17).ClearContents
Application.EnableEvents = True
.Unprotect
'.Range(sHide2 & 1).EntireColumn.Hidden = True
.Range(sHide2).EntireColumn.Hidden = True
.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
.EnableSelection = xlUnlockedCells
End With
UserForm1.Show
'Enable Timers on Workbook Open
bGblInhibitTimers = False
'Stop all timers
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
Application.OnTime RunStatusBarWhen, "TimeTilExitTimer", , False
On Error GoTo 0
'Arm Timer to save and close workbook
RunWhen = Now + TimeSerial(0, NUM_MINUTES, NUM_SECONDS)
Application.OnTime RunWhen, "SaveAndClose", , True
'Arm Timer to display time remaining
RunStatusBarWhen = Now + TimeSerial(0, 0, STATUS_BAR_REFRESH_TIME_IN_SECONDS)
Application.OnTime RunStatusBarWhen, "TimeTilExitTimer", , True
Application.Iteration = True
Application.MaxIterations = 1
Application.MaxChange = 0.001
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
'Display Time Remaining Only When timers are enabled
If bGblInhibitTimers = False Then
RunWhen = Now + TimeSerial(0, NUM_MINUTES, NUM_SECONDS)
Application.OnTime RunWhen, "SaveAndClose", , True
End If
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
On Error GoTo 0
'Display Time Remaining Only When timers are enabled
If bGblInhibitTimers = False Then
RunWhen = Now + TimeSerial(0, NUM_MINUTES, NUM_SECONDS)
Application.OnTime RunWhen, "SaveAndClose", , True
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Stop all timers
On Error Resume Next
Application.OnTime RunWhen, "SaveAndClose", , False
Application.OnTime RunStatusBarWhen, "TimeTilExitTimer", , False
On Error GoTo 0
End Sub
Thanks in Advance
Bookmarks