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

> 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
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "terilad" wrote:
>
> > 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
> >

You didn't like yesterday's suggestions?

terilad wrote:
>
> 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

--

Dave Peterson

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

> You didn't like yesterday's suggestions?
>
> terilad wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>

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

terilad wrote:
>
> 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" wrote:
>
> > You didn't like yesterday's suggestions?
> >
> > terilad wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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

"Jacob Skaria" wrote:

> 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
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "terilad" wrote:
>
> > 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
> >