VBA: Save and Close vs Deactivate

[Dragonmann]

I have created a special worksheet template for filing new customer RFQs.

When the required information is filled in, a submit button appears.

When the button is pressed the document opens the log file, copies some info into it, and then saves and closes both programs.


The problem is I have cod in the Worksheet_Deactivate event, specifically unhiding the ribbon bar, but I have tried other code as a test. If there is any code in the Deactivate event I get "Run time Error '1004': Method 'Close' of object '_workbook' failed"


I was able to make it work by using Application.EnableEvents = False , but since this workbook is closing it leaves the events off, and messes up the next excel file I open.


Any suggestions of code to disable only the deactivate event, or just how to make it work in general?


This is the end of the submit code:

ActiveWorkbook.SaveAs Filename:="c:\workspace\RFQ" & RFQNumber & " - " & CompanyName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    
    Call SendMail((RFQNumber), (CompanyName), (RequestBy), (RFQDate))
    
    If ActiveWorkbook.FileFormat <> xlOpenXMLTemplateMacroEnabled Then
    
        ActiveSheet.Protect


    End If
    
    
    
    
    ActiveWorkbook.Unprotect
    
            ActiveWindow.DisplayGridlines = True
            ActiveWindow.DisplayHeadings = True
            Application.DisplayFormulaBar = True
            Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
    
    Application.EnableEvents = False
        
    ActiveWorkbook.Close SaveChanges:=False
    
    Application.EnableEvents = True

And this is the Deactivate code:

    Application.DisplayFormulaBar = True
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"

 

[StephenCrump]

In very general terms:

Public bAbort As Boolean
Sub DoStuff()

    bAbort = True
    
    'Do Stuff that will trigger EventTriggered
    
    bAbort = False

End Sub

and then in the event handler:

Sub EventTriggered()

    If bAbort Then Exit Sub
    
    'Handle event here
        
End Sub

 

[Dragonmann]

Back in the office Monday morning at that worked perfectly. Thanks for the help