BeforeSave Event

  • Thread starter Thread starter terilad
  • Start date Start date
T

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
 
Excellant many thanks.

Regards

Mark

Jacob Skaria said:
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
Click to expand...
 
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
Click to expand...
 
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
 
You may want to review yesterday's suggestion. There were some things in there
that may be useful.
 
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
 
Back
Top