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

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

--
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
>
>

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

Mark

"john" wrote:

> 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
>
> --
> 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
> >
> >

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
>
>

How do you mean stop working?

Only change I suggested was to add

Else

Cancel = True

in your If statement.


--
jb


"terilad" wrote:

> This code has caused Microsoft excel to stop working when I click save as new
> filename.
>
> Mark
>
> "john" wrote:
>
> > 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
> >
> > --
> > 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
> > >
> > >

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

"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
> >
> >

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

"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
> >
> >

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
> >
> >