BeforeSave

[terilad]

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

 

[john]

see if this does what you want?


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

End If

Application.EnableEvents = True

End Sub

 

[terilad]

This code has caused Microsoft excel to stop working when I click save as new
filename.

Mark

 

[john]

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

 

[john]

How do you mean stop working?

Only change I suggested was to add

Else

Cancel = True

in your If statement.

 

[terilad]

This code is still causing excel to stop working when selected yes to saveas
new filename, I think it may have something to do with trying to recover
workbook, as it is being saved as a new name.

Any ideas how to resolve this issue

Mark

 

[terilad]

Excel is obviously creating a backup because of this code as when I open the
new workbook that has saved with new filename it is also opening previous
workbook as backup.

Any ideas how to resolve.

Mark

 

[terilad]

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