Closing Excel..

[Sky86]

HI,

I need some help on this, i want to clean all the result before closing the excel file.
The code is like this,

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Please ensure to copy the result to a new excel before close," & Chr(10) & "or else the result will be delete. " & Chr(10) & "Do you want to close the application now? ", vbOKCancel, "Warning") = vbOK Then
Cleanrecord
ActiveWorkbook.Close (True)
End If
End Sub

The problem is, when i click the cancel button. Excel will save and close the workbook.
is there a way to stop the closing.

Thanks.

[anandbohra]

try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim a
a = MsgBox("Please ensure to copy the result to a new excel before close," & Chr(10) & "or else the result will be delete. " & Chr(10) & "Do you want to close the application now? ", vbOKCancel, "Warning")

If a = vbOK Then
Cleanrecord
ActiveWorkbook.Close (True)
Else
Cancel = True
End If
End Sub

[anandbohra]

your same code add this line
Else
Cancel = True

after
ActiveWorkbook.Close (True)


so full code will be


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Please ensure to copy the result to a new excel before close," & Chr(10) & "or else the result will be delete. " & Chr(10) & "Do you want to close the application now? ", vbOKCancel, "Warning") = vbOK Then
Cleanrecord
ActiveWorkbook.Close (True)
Else
Cancel = True
End If
End Sub

[Sky86]

Thanks anandbohra,

It's work.
i can continue my work already.

Thanks.

[Sky86]

Hi,

why my message box come out twice, if i click the 'OK' button. Excel will dispaly the message box again.

where is the problem.
hope you can help.

Thanks.

[mdmackillop]

Step through the code to see the sequence of events, that will give you an understanding.
Then change this line.
ActiveWorkbook.Close (True)
to
ActiveWorkbook.Save

[Sky86]

md,

Thanks, i know wat is the problem already.
because i use ActiveWorkbook.Close.
when the coding generate until this line, it will run the coding again.

Thanks.