Hey Guys,

I have this code in a module. If I have this workbook open and another one and if the other one is active and I close excel with the red x in the corner I get a runtime error with the bit below in red highlighted. I assume I need to add a "Thisworkbook.active" but it doesn't seem to work. I ave tried it in the this workbook code under before close and also in the below module but neither worked. 

Option Explicit
Option Compare Text

'// Moved from the ThisWorkbook class - Do you really need
'// 2 constants containing the same value?
Public Const wsWarningSheet As String = "Splash"
Public Const sSheetNameThatMUST_REMAIN_VISIBLE = "Splash"

Sub SheetProtection(Optional LockIt As Boolean = True, _
                    Optional LockSheet As String = vbNullString, _
                    Optional SelectUnLocked As Boolean = True)

   '// Stores a list of worksheets to lock/Unlock
   Dim wsC              As Collection
   Dim ws               As Excel.Worksheet
   Dim cE As clsEvents
   
   Set cE = New clsEvents
   Application.ScreenUpdating = False
   
   '// Add Password here if using ... ="Password"
   Const PASSWRD = "test"

   '// The biggest change... the USERINTERFACEONLY parameter
   '// This allows code to change a worksheet even if protected
   '// The protection only applies to changes made in the
   '// User Interface...

   '// Passing the name of a worksheet in LOCKSHEET will only
   '// lock that sheet. If LOCKSHEET is blank then all sheets
   '// are locked

   '//Even though I may not have anticipated your requirements
   '// correctly, it should be possible to use this as a single
   '// means of protection rather than having PROTECT and UNPROTECT
   '// statements all over the place.
   Set wsC = New Collection
   If LockSheet = vbNullString Then
      For Each ws In Worksheets
         wsC.Add ws
      Next
   Else
      wsC.Add Sheets(LockSheet)
   End If

   For Each ws In wsC
      With ws

         If LockIt Then
            .Protect Password:=PASSWRD, _
                     DrawingObjects:=False, _
                     Contents:=True, _
                     Scenarios:=False, _
                     AllowFormattingCells:=True, _
                     AllowFormattingColumns:=True, _
                     AllowFormattingRows:=True, _
                     AllowSorting:=True, _
                     AllowFiltering:=True, _
                     AllowUsingPivotTables:=True, _
                     UserInterfaceOnly:=True

            If SelectUnLocked Then
               .EnableSelection = xlUnlockedCells
            End If
         Else
            .Unprotect Password:=PASSWRD
         End If
      End With

   Next ws

End Sub

Public Sub SheetHide(HideIt As Boolean, Optional ShowSheet As String = vbNullString)

   Dim ws               As Excel.Worksheet


   '// If the SHOWSHEET parameter is blank then the sheet wsWarningSheet is
   '// is assumed to be the only 1 visible, otherwise the passed sheet will
   '// be the only visible sheet
   
   If ShowSheet = vbNullString Then ShowSheet = wsWarningSheet

   Sheets(ShowSheet).Visible = xlSheetVisible
   For Each ws In ThisWorkbook.Worksheets
      If ws.Name <> ShowSheet Then
         ws.Visible = Not HideIt
      End If
   Next

End Sub

Public Sub SheetShow(Level As Integer, LandingSht As String)

   Dim c As Excel.Range
   
   '// temporary as not all sheets exist in this copy
   On Error Resume Next
   ' On Error GoTo Catch
   
   '// Hide all sheets except the default
   SheetHide True
   
   For Each c In shtLevels.Columns(Level).Cells.SpecialCells(xlCellTypeConstants)
      Sheets(c.Value).Visible = xlSheetVisible
   Next
   
   '// Ignore any errors - it'll just remain at the current sheet
   If LandingSht <> vbNullString Then
      Sheets(GetSheetNameFromCodeName(LandingSht)).Activate
   End If
   
Catch:
   
End Sub

Public Function GetSheetNameFromCodeName(cn As String) As String

   '// About the only weakness of using CodeNames is you cannot
   '// set a reference to it using a string variable.
   '// This retruns the Current Tab name for the sheet
   
   Dim ws As Excel.Worksheet
   For Each ws In ThisWorkbook.Worksheets
   
      If ws.CodeName = cn Then
         GetSheetNameFromCodeName = ws.Name
         Exit Function
      End If
   Next
   
End Function



'//Just test procs below

Public Sub ResetEnv()

   With Application
      .DisplayAlerts = True
      .ScreenUpdating = True
      .EnableEvents = True
   End With
   
End Sub

Sub TestProc()
   
   Dim csv As clsSheetVis
   Dim cE As clsEvents
   
   Set cE = New clsEvents
   Set csv = New clsSheetVis
   ThisWorkbook.Save
   csv.Reset
   Set cE = Nothing
   
End Sub

Sub MakeLevel()

   Dim v As Variant
   v = Array("Splash", "Bank Transactions", "CC Transactions", "Accountant", "Mileage", "Summary")
   ActiveCell.Resize(UBound(v) + 1).Value = WorksheetFunction.Transpose(v)
   
End Sub