Workbook_BeforeClose problem
 

Hi
In this part of the code

exitmessage = "Do you want to save the changes you made to " &
Range("c10") & Range("c7") & ".xls"

which workbook are these range references from ?
They could be from the active book, which is not currently
Lead ATSM Ver3.51.xls

This may cause a problem.

HTH
Andrew Bourke


broogle wrote:
What is wrong with this code? Please help?

This code will prompt user with "Yes No Cancel", user can choose to
exit and save the activeworkbook (file name will be taken from certain
cells) or exit without saving it.
My problem is the code will run twice before it close, this is because
I make enableevents = true before it close. If I make enableevents =
false, the next time I open another spreadsheet the code will not be
activated. (Apologise for my bad english). Thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.DisplayAlerts = False

Application.EnableEvents = False
If ThisWorkbook.Name = "Lead ATSM Ver3.51.xls" Then
ActiveWorkbook.Close (False)
Else
Dim exitmessage, msg, response As String

exitmessage = "Do you want to save the changes you made to " &
Range("c10") & Range("c7") & ".xls"

response = MsgBox(exitmessage, vbYesNoCancel)

If response = vbNo Then

ActiveWorkbook.Close (False)
Else

If response = vbYes Then


ActiveWorkbook.SaveCopyAs filename:= _
"C:\Temp" & "\" & Range("c10") & Range("c7") & ".xls"

msg = "File was saved as C:\Temp\" & Range("c10") &
Range("c7") & ".xls"
response = MsgBox(msg, vbExclamation)
Application.EnableEvents = True

ActiveWorkbook.Close (False)

Exit Sub
Else

Cancel = True

End If
End If
End If


End Sub