VBA: Sub Workbook_BeforeClose for deleting the data

user_14002405 0
Viewed 95

I'm deleting the data in my speadsheet befor close it like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("1 - Feuille de Suivi Commercial").Range("GET_NOM_CONTRACTANT_C4") = ""
Sheets("1 - Feuille de Suivi Commercial").Range("C6") = ""
Sheets("1 - Feuille de Suivi Commercial").Range("GET_EFFET_SOUSCRIPTION_C7") = ""

....

But I would like to do that after the message :

enter image description here

I mean that I would like to make a choice : save as or not, and close the speardsheet with the data.

I don't know how to improve the code.

Thank you very much for your help !

vba excel
2 Answers

BeforeClose is the last accessible event, but not the last.

You may want to store the Saved property of the workbook and then reset it after your routine.

Something like:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
   dim wasSaved as Boolean

   wasSaved=ThisWorkbook.Saved
   ' do your stuff
   ThisWorkbook.Saved=wasSaved

end sub

In this way, Excel will ask for saving only if the workbook wasn't saved.

user_137593870

Taking Control Over Closing a Workbook

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim msg As Variant
    msg = MsgBox("Do you wanna save with clearing the ranges?", _
        vbInformation + vbYesNoCancel, "Save Workbook")
    Select Case msg
    Case vbYes ' Range is 'emptied' and the workbook is saved and closed.
        With Sheets("1 - Feuille de Suivi Commercial")
            .Range("GET_NOM_CONTRACTANT_C4") = ""
            .Range("C6") = ""
            .Range("GET_EFFET_SOUSCRIPTION_C7") = ""
        End With
        Me.Save
    Case vbNo
        If Not Me.Saved Then
            ' Either, if you don't wanna save the changes before closing...
            'Me.Saved = True
            ' ..., or if you wanna save as is before closing:
            Me.Save
        End If
    Case vbCancel ' You've changed your mind: nothing will happen.
        Cancel = True
    End Select
End Sub
user_98140690
Related