About Us

terilad

Fixed it, changed the cancel = true and false round and works

"john" wrote:

sorry, did not fully read your post - you want the file to close without
saving changes when NO press?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strPath As String
Dim strFilename As String
Dim userResponse As Variant

Application.EnableEvents = False

strFilename = "Kelso Resources WC " & _
Format(Sheets("KelsoResources").Range("N2"), "dd-mmm-yy")

userResponse = MsgBox("Do you want to save as " & _
strFilename, vbYesNo + vbInformation, _
"Kelso Operational Resources Â© MN ")

If userResponse = vbYes Then

strPath = "C:\Users\Mark\Desktop"

strFilename = strPath & "\" & strFilename & ".xls"

ThisWorkbook.SaveAs Filename:=strFilename, _
FileFormat:=xlNormal, _
CreateBackup:=False

Cancel = False

Else

Cancel = True

Application.EnableEvents = True

ThisWorkbook.Close False

End If

Application.EnableEvents = True

End Sub

--
jb

"terilad" wrote:

Hi,

I need some more help with this code when msg box appears do you want to
save as etc I click yes thats fine it saves as the new filename, but when I
click No it is still saving changes in workbook to old filename, I need the
workbook to close without any changes made if No is selected in msg box, can
you help with this.

Here is the code:

End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strPath As String
Dim strFilename As String
Dim userResponse As Variant
Application.EnableEvents = False
strFilename = "Kelso Resources WC " & Format(Sheets("Kelso
Resources").Range("N2"), "dd-mmm-yy")
userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo +
vbInformation, "Kelso Operational Resources Â© MN ")
If userResponse = vbYes Then
strPath = "C:\Users\Mark\Desktop"
strFilename = strPath & "\" & strFilename & ".xls"
ThisWorkbook.SaveAs Filename:=strFilename, FileFormat:=xlNormal,
CreateBackup:=False
Cancel = False
End If
Application.EnableEvents = False
End Sub

Regards

Mark

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
beforesave	Doug	Excel Programming	4	June 11th 09 07:49 PM
beforesave	Patrick Molloy	Excel Programming	0	June 11th 09 06:53 PM
BeforeSave	Steven	Excel Programming	2	January 21st 06 05:37 PM
BeforeSave Sub	Phil Hageman[_3_]	Excel Programming	6	January 14th 04 10:12 AM
VBA - BeforeSave - NEED HELP	HRobertson	Excel Programming	2	October 23rd 03 06:50 PM

Menu

About Us