question about Sub Workbook_BeforeClose

[danmen]

I've written some code to be executed right before I close my workbook however I'm having trouble in the way things get processed

what happens:

1. I close the workbook
2. my coding in Sub Workbook_BeforeClose gets executed
3. Excel asks me if I want to save changes ( yes, no, cancel )

what I'd like to happen:

1. I close the workbook
2. Excel asks me if I want to save changes ( yes, no, cancel )
3. If I select yes or no my coding gets executed and Excel is closed
4. If I select Cancel my coding doesn't get executed and I return to Excel

can anyone show me how to set this up properly?

thx. for any help! =)

 

[Gary McMaster]

Like this?

Gary

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
Dim iAnswer As Integer
 
iAnswer = MsgBox("Save changes?", vbYesNoCancel + vbInformation, 
"Save file")
 
Select Case iAnswer
 
    Case vbYes
        'Save the file & close
        ThisWorkbook.Close SaveChanges:=True
    
    Case vbNo
        'Close the file, Do not save
        ThisWorkbook.Close SaveChanges:=False
    
    Case vbCancel
        MsgBox "Command aborted!"
        Exit Sub
    
End Select
 
End Sub

 

[AlphaFrog]

Try something like this...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Reply As Long
    
    Reply = MsgBox("Do you want to save changes?", vbYesNoCancel, "Save Changes")
    
    Select Case Reply
        Case vbYes
            'Save changes here
            Call MyCode
            ThisWorkbook.Save
        Case vbNo
            'Don't save changes and exit
            Call MyCode
            ThisWorkbook.Saved = True
        Case vbCancel
            'Don't exit
            Cancel = True
    End Select
    
End Sub

Private Sub MyCode()
    ' YOUR CODE HERE
    ' This code doesn't save changes
End Sub

EDIT: I'm a day late and a dollar short again I see.

 

[JoeMo]

Alternative, slightly shorter version:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans As Integer
ans = MsgBox("Do you want to save changes?", vbYesNoCancel)
Select Case ans
    Case vbCancel
        Cancel = True
        Exit Sub
    Case Else
End Select
'Put your code here
If ans = vbYes Then Me.Save
End Sub

 

[danmen]

thx. guys ... I tried AlphaFrog's version first and it works great (!) now supposing I'd like the worksheet name to appear ie:



instead of:

Reply = MsgBox("Do you want to save changes?", vbYesNoCancel, "Save Changes")


I want:

Reply = MsgBox("Do you want to save changes to WORKSHEET NAME HERE?", vbYesNoCancel, "Save Changes")



how would the coding of the second line look like then? thx. again to all!


OK I FIGURED IT OUT (LOL)

Reply = MsgBox("Do you want to save changes to " & ThisWorkbook.Name & "?", vbYesNoCancel, "Save Changes")

 

[AlphaFrog]

Reply = MsgBox("Do you want to save changes to " & Thisworkbook.Name & "?", vbYesNoCancel, "Save Changes")