BeforeClose event fires and runs code but does not cleanup

[spence524]

When the user opens my application, some tidying up is done:

Private Sub Workbook_Open()

    shtMain.Activate
    ExcelLockDown

End Sub



Sub ExcelLockDown()

    With Application
        .CellDragAndDrop = False
        .CutCopyMode = False
        .OnKey "^c", ""
        .OnKey "^v", ""
        .StatusBar = "Current User: (" & UCase(Environ("Username")) & ")"
    End With
    
    With ActiveWindow
        .DisplayWorkbookTabs = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
        .DisplayHeadings = False
    End With
    
    
End Sub

This code works fine. It is when the user closes the workbook that I have problems. Either by clicking the X in the top right to close, or my programmed close button which calls a macro that runs """ ThisWorkbook.Close False """ The problem is that all of these locked down tidy-up items do not get reset. i stepped through the below code and each line runs, it just doesn't do anything.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.ScreenUpdating = False

    ExcelReset
    
    Application.EnableEvents = False
    shtLanding.Activate
    
    Me.Saved = True

End Sub



Sub ExcelReset()

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .CellDragAndDrop = True
        .CutCopyMode = True
        .OnKey "^c"
        .OnKey "^v"
        .StatusBar = False
    End With

    With ActiveWindow
        .DisplayWorkbookTabs = True
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayHeadings = True
    End With
    
End Sub

Each of the lines in the ExcelReset macro runs as I step through the code, but the tabs don't appear, the status bar doesn't get reset, nothing. Looking for some expert direction, thanks.

 

[mikerickson]

Your Before-Close routine may be resetting the sheets, but it isn't saving that saved version.

The Me.Saved line is saying "act like you were saved", it is not saving the workbook.

Try replacing that with Me.Save

 

[spence524]

I actually don't want it to save at all, that's why I wanted to mark the wb as saved so it wouldn't trigger the save dialog. I took that line out completely and I still have the problem.

 

[mikerickson]

The tab settings etc are for the ActiveWindow (i.e. the window of the workbook that you don't want to save).

If you insert a line, does it return what you expect?

MsgBox ActiveWorkbook.Caption

 

[spence524]

I did not find this property, but tried ActiveWorkbook.name and activewindow.caption. Those both returned as expected. If i run my ExcelReset macro outside of the BeforeClose event, it works just right. It seems to be when the ExcelReset sub is called from the event handler that it doesn't do it. Again, the sub runs and steps through the lines, just doesn't make the change.

 

[Jaafar Tribak]

Seeif this works for you ( Code changes in red)

[B][COLOR=#ff0000]Private bBool As Boolean[/COLOR][/B]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
[B][COLOR=#ff0000]    bBool = False[/COLOR][/B]
    Application.ScreenUpdating = False
[COLOR=#ff0000][B]    Application.OnTime Now, Me.CodeName & ".ExcelReset"[/B][/COLOR]
    Application.EnableEvents = False
    shtLanding.Activate
[B][COLOR=#ff0000]    Do[/COLOR][/B]
[B][COLOR=#ff0000]        DoEvents[/COLOR][/B]
[B][COLOR=#ff0000]    Loop Until bBool = True[/COLOR][/B]
    Me.Saved = True
End Sub

Sub ExcelReset()
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .CellDragAndDrop = True
        .CutCopyMode = True
        .OnKey "^c"
        .OnKey "^v"
        .StatusBar = False
    End With
    With ActiveWindow
        .DisplayWorkbookTabs = True
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayHeadings = True
    End With
[B][COLOR=#ff0000]    bBool = True[/COLOR][/B]
End Sub