PDA

View Full Version : Closing Excel..



Sky86
09-23-2007, 07:16 PM
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
09-23-2007, 09:48 PM
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
09-23-2007, 09:50 PM
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
09-23-2007, 10:23 PM
Thanks anandbohra,

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

Thanks.

Sky86
09-23-2007, 10:33 PM
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
09-24-2007, 05:35 AM
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
09-24-2007, 10:24 PM
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.