BeforeSave Event

[terilad]

Hi,

I have a code below and have a little problem with it, its not running
smoothly for me.

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

Before save event, when I click on save it pops up the msg box do you want
to save as, when I click yes it pops up again and excel stops working, do you
have any ideas if my code is wrong.

Regards


Mark

 

[terilad]

Excellant many thanks.

Regards

Mark

Try the below code....Please note the changes Application.EnableEvents =
True/False and 'Cacel =True'


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 = "Galashiels Resources WC " & Format(Sheets("Galashiels
Resources").Range("N2"), "dd-mmm-yy")
userResponse = MsgBox("Do you want to save as " & strFilename, vbYesNo +
vbCritical, "Galashiels 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 = True
End If
Application.EnableEvents = True

End Sub

 

[Dave Peterson]

You didn't like yesterday's suggestions?

Hi,

I have a code below and have a little problem with it, its not running
smoothly for me.

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

Before save event, when I click on save it pops up the msg box do you want
to save as, when I click yes it pops up again and excel stops working, do you
have any ideas if my code is wrong.

Regards

Mark

 

[terilad]

Hi Dave,

I couldn't get yesterdays solutions or codes to work as stated in todays
discussion, and I was looking for help to revise my code as I was having
problems with 2 pop up boxes and excel stopping. I did rate your answers on
the 8th with your 2 responses and altered my code accordingly as Sheet1 had a
different name and my file path was missing an s from user, so your responses
to my questions were of great help, code only needed additional slight
modification to resolve the issues with 2 pop up boxes and excel stopping. I
am learning slowly with VBA. Thankyou again for your input, all help is
greatfully appreciated.

Thanks

 

[Dave Peterson]

You may want to review yesterday's suggestion. There were some things in there
that may be useful.

 

[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