workbook before close

[mohavv]

I have folowing code

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Range("G2").Value = "UNBALANCED!" Then Response = MsgBox("ATTENTION
UNBALANCED! Yes to go ahead / No to Cancel and correct.", vbYesNo)

If Rensponse = vbNo Then Exit Sub
If Response = vbYes Then

End if
End Sub

What I want to achieve is: when pressed "No" to go back to sheet, to
stop closing procedure
When pressed "Yes" close workbook

Is this possible?

Cheers,
Harold

 

[carlo]

Hi harold

basically this structure should help you

-------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)

If MsgBox("bla", vbYesNo) = vbNo Then
Cancel = True
End If

End Sub
------------------------------------------------------------

just change the msgbox to your wishes.

hth

Carlo

 

[Gord Dibben]

Sub Workbook_BeforeClose(Cancel As Boolean)
With ActiveWorkbook
If Sheets("Sheet1").Range("G2").Value = "Unbalanced" Then
msg = "ATTENTION UNBALANCED! Do You Want Save Changes? Yes to Save. "
msg = msg & "No to Cancel and Correct."
ans = MsgBox(msg, vbQuestion + vbYesNo)
Select Case ans
Case vbYes
Me.Save
Case vbNo
Cancel = True
Exit Sub
End Select
End If
End With
End Sub


Gord Dibben MS Excel MVP