VBA: Save and Close vs Deactivate

Dragonmann

New Member
Joined
Apr 26, 2010
Messages
14
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:

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:

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

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,756
Office Version
365
Platform
Windows
In very general terms:

Code:
Public bAbort As Boolean
Sub DoStuff()

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

End Sub
and then in the event handler:

Code:
Sub EventTriggered()

    If bAbort Then Exit Sub
    
    'Handle event here
        
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,045
Messages
5,466,222
Members
406,473
Latest member
Dunno123

This Week's Hot Topics

Top