CODEWITHSUNDEEP
vbaexcelexcel-2010
NJMR
NJMR

Reputation: 1964

Prevent closing of workbook

There is ABC.xls file which has the macro. Now, the macro has a sub which is getting called when I press Ctrl + M. This sub will open a Open File Dialog where the user can choose a CSV file. So basically, this macro is used for processing and saving CSV file. Below is the code which gets called on pressing Ctrl + M. 

Sub runProperChangeSubroutine()             ' Assigned to shortcut key CTRL + M.
    file_name = ActiveWorkbook.Name         ' Gets the file name.
    Application.Run file_name & "!ChangeSub"
End Sub

When the user closes the workbook, I have to test for a condition like each row in the CSV has a termination string. If that termination string is not present, I should popup a message box to the user and prevent the closing of workbook. So I did the following...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim improperRowNumber As Integer
    Dim BlnEventState As Boolean
    improperRowNumber = returnImproperRow()

    BlnEventState = Application.EnableEvents
    Application.EnableEvents = True

    If improperRowNumber <> -1 Then
        Cancel = True
        MsgBox "The row number " & improperRowNumber & " is not ending with '@/#'. Please correct the row before closing."
    Else
        Call saveCSV
    End If
    Application.EnableEvents = BlnEventState
End Sub

On clicking of the close (X mark on top right corner, closing of workboox. I am not closing the excel.) button, I am able to see the message box but the workboox closes. I want user to make some edit if the row is not properly ended. Please suggest.

Edited: As the above code is not working, I used ThisWorkbook.Saved = False after the message box as shown below...

If improperRowNumber <> -1 Then
    MsgBox "The row number " & improperRowNumber & " is not ending with '@/#'. Please correct the row before closing."
    Application.DisplayAlerts = False
    ThisWorkbook.Saved = False
    Cancel = False
Else

Now it displays the "Do you want to save the changes...." message box. If I click on Cancel button on message box, the workbook is not closed.

Is there a way to customize the message box text or buttons or hide this Save message box?

Upvotes: 7

Views: 7499

Answers (5)

amitklein
amitklein

Reputation: 1395

You can use this code in the workbook macro 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Save
Workbooks.Open Application.ActiveWorkbook.FullName
End Sub

As you can see it basically saves and open the workbook when it close

Upvotes: 0

Excelosaurus
Excelosaurus

Reputation: 2849

If you must monitor the closing of workbooks other than the one containing the macros, you can intercept application-level events from your macro-enabled workbook as follows:

Add (or adapt) this code behind ThisWorkbook:

Option Explicit

Private m_CloseHelper As CloseHelper

Private Sub Workbook_Open()
    Set m_CloseHelper = New CloseHelper
End Sub

Add a new Class Module named CloseHelper:

Option Explicit

Private WithEvents m_App As Excel.Application

Private Sub Class_Initialize()
    Set m_App = Excel.Application
End Sub

Private Sub Class_Terminate()
    Set m_App = Nothing
End Sub

Private Sub m_App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    'Logic goes here, e.g. the code below will prevent the user from closing
    'any workbook other than this one, for as long as this workbook is open.
    If Not Wb Is ThisWorkbook Then
        Cancel = True
        MsgBox "Hello from m_App_WorkbookBeforeClose"
    End If
End Sub

The important keyword is WithEvents, and the principle is that events raised by the Excel application can now be coded against within the CloseHelper class.

You'll find a more in-depth article here: Application Events

Upvotes: 6

DecimalTurn
DecimalTurn

Reputation: 4129

Have you tried closing all your add-ins (or run Excel in Safe Mode)?

I'm asking because this problem could be due to the fact that one of your add-ins contains an Application Level Event like the one described in Excelosaurus' answer. And it could contain Application.EnableEvents = False or even Cancel = False and interfere with your code.

EDIT: This would be especially hard to detect with an add-in that is protected since the code would run after Workbook_BeforeClose and you won't be able to know what it's doing even if you were debugging line by line using F8.

Upvotes: 0

Michael
Michael

Reputation: 4858

The Workbook_BeforeClose event only applies to the workbook in which the code is located. It runs when you try to close the macro workbook and setting its parameter Cancel to TRUE only cancels the closure of the macro workbook when you close Excel. The csv file that is open is a separate workbook and Excel tries to close that separately, generating a separate message about saving the csv file.

To automatically prevent the csv file from being closed, you need to use extensibility programming to add a Workbook_BeforeClose event to the csv file while it is open in Excel. Obviously you can't save the file with the event in place, but you don't need to. The code sitting in the module will run while the workbook is still open preventing closure when your conditions are not met; and a successful save allowed after conditions are met will be in the .csv format which will wipe the temporarily added code.

http://www.cpearson.com/excel/vbe.aspx

Upvotes: 0

Variatus
Variatus

Reputation: 14383

Pressing the X-button in the top right quits the Excel application. You shouldn't expect the workbook to remain open when the application is closed. You could write an application-event procedure preventing Excel from quitting, but the better way might be to simply get rid of the bad habit of using that button. Find another way to close the workbook.

Upvotes: 0

Related Questions