Prompting a predefined filename to be saved

Bart V · 07-18-2006, 05:45 PM

I am using beforesaveas to prompt the save as screen with a pre-defined name.
When using the save button everything seems to be functioning ok as I am
using Cancel = True. -When using the save as function I get the pre-defined
name, however after confirming, using the save button, the screen pops up a
second time. As this file needs to be sent to end-users I don't want this to
happen. What can be done to avoid this? Find below the subroutine.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
NameToSave = Sheet15.Cells(5, 2) & " - CDP - " & Sheet15.Cells(3, 2) &
" (v" & Year(Date) & Month(Date) & Day(Date) & ")"
If Sheet15.Cells(3, 2) = "" Then
Answer = MsgBox("Before being able to save this file you need to
select a year in the parameter sheet.", Buttons:=48)
Cancel = True
Sheet15.Activate
Exit Sub
End If

If Sheet15.Cells(5, 2) = "" Then
Answer = MsgBox("Before being able to save this file you need to
select a country in the parameter sheet.", Buttons:=48)
Cancel = True
Sheet15.Activate
Exit Sub
End If

If SaveAsUI = False Then
If MsgBox("Do you really want to save this Workbook? ", vbYesNo) =
vbNo Then
Cancel = True
Exit Sub
Else
Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
Cancel = True
Exit Sub
End If
Else
Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
End If

End Sub

NickHK · 07-18-2006, 11:15 PM

Bart,
Assuming everything is successful, the file is saved through your last line
of code.
However, you are in the _BeforeSave event and you have not cancelled the
reason that event was called.
So, add a Cancel=True

Also, do you all the SaveAsUI checks at all ?

NickHK

"Bart V" <BartV@discussions.microsoft.com> wrote in message
news:77A25BF5-8193-4275-9D1E-209917DBB8D6@microsoft.com...
> I am using beforesaveas to prompt the save as screen with a pre-defined
name.
> When using the save button everything seems to be functioning ok as I am
> using Cancel = True. -When using the save as function I get the
pre-defined
> name, however after confirming, using the save button, the screen pops up
a
> second time. As this file needs to be sent to end-users I don't want this
to
> happen. What can be done to avoid this? Find below the subroutine.
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> Cancel As Boolean)
> NameToSave = Sheet15.Cells(5, 2) & " - CDP - " & Sheet15.Cells(3, 2)
&
> " (v" & Year(Date) & Month(Date) & Day(Date) & ")"
> If Sheet15.Cells(3, 2) = "" Then
> Answer = MsgBox("Before being able to save this file you need to
> select a year in the parameter sheet.", Buttons:=48)
> Cancel = True
> Sheet15.Activate
> Exit Sub
> End If
>
> If Sheet15.Cells(5, 2) = "" Then
> Answer = MsgBox("Before being able to save this file you need to
> select a country in the parameter sheet.", Buttons:=48)
> Cancel = True
> Sheet15.Activate
> Exit Sub
> End If
>
> If SaveAsUI = False Then
> If MsgBox("Do you really want to save this Workbook? ", vbYesNo) =
> vbNo Then
> Cancel = True
> Exit Sub
> Else
> Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> Cancel = True
> Exit Sub
> End If
> Else
> Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> End If
>
> End Sub

Bart V · 07-19-2006, 02:30 AM

Nick,

I tried this already, but it did not work. I guess the problem is that I use
the save as button. With the save button everything works fine.

Bart V

"NickHK" wrote:

> Bart,
> Assuming everything is successful, the file is saved through your last line
> of code.
> However, you are in the _BeforeSave event and you have not cancelled the
> reason that event was called.
> So, add a Cancel=True
>
> Also, do you all the SaveAsUI checks at all ?
>
> NickHK
>
> "Bart V" <BartV@discussions.microsoft.com> wrote in message
> news:77A25BF5-8193-4275-9D1E-209917DBB8D6@microsoft.com...
> > I am using beforesaveas to prompt the save as screen with a pre-defined
> name.
> > When using the save button everything seems to be functioning ok as I am
> > using Cancel = True. -When using the save as function I get the
> pre-defined
> > name, however after confirming, using the save button, the screen pops up
> a
> > second time. As this file needs to be sent to end-users I don't want this
> to
> > happen. What can be done to avoid this? Find below the subroutine.
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> > Cancel As Boolean)
> > NameToSave = Sheet15.Cells(5, 2) & " - CDP - " & Sheet15.Cells(3, 2)
> &
> > " (v" & Year(Date) & Month(Date) & Day(Date) & ")"
> > If Sheet15.Cells(3, 2) = "" Then
> > Answer = MsgBox("Before being able to save this file you need to
> > select a year in the parameter sheet.", Buttons:=48)
> > Cancel = True
> > Sheet15.Activate
> > Exit Sub
> > End If
> >
> > If Sheet15.Cells(5, 2) = "" Then
> > Answer = MsgBox("Before being able to save this file you need to
> > select a country in the parameter sheet.", Buttons:=48)
> > Cancel = True
> > Sheet15.Activate
> > Exit Sub
> > End If
> >
> > If SaveAsUI = False Then
> > If MsgBox("Do you really want to save this Workbook? ", vbYesNo) =
> > vbNo Then
> > Cancel = True
> > Exit Sub
> > Else
> > Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> > Cancel = True
> > Exit Sub
> > End If
> > Else
> > Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> > End If
> >
> > End Sub
>
>
>

NickHK · 07-19-2006, 02:55 AM

Bart,
Look at your code:
If SaveAsUI = False Then
If MsgBox("Do you really want to save this Workbook? ", vbYesNo) =
vbNo Then
Cancel = True
Exit Sub
Else
Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
Cancel = True
Exit Sub
End If
Else
'************* This is where you will perform the Save
Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
'************ There is no Cancel=True
End If

NickHK

"Bart V" <BartV@discussions.microsoft.com> wrote in message
news:C02E12FD-DFBC-427B-AB52-0A0484D74452@microsoft.com...
> Nick,
>
> I tried this already, but it did not work. I guess the problem is that I
use
> the save as button. With the save button everything works fine.
>
> Bart V
>
> "NickHK" wrote:
>
> > Bart,
> > Assuming everything is successful, the file is saved through your last
line
> > of code.
> > However, you are in the _BeforeSave event and you have not cancelled the
> > reason that event was called.
> > So, add a Cancel=True
> >
> > Also, do you all the SaveAsUI checks at all ?
> >
> > NickHK
> >
> > "Bart V" <BartV@discussions.microsoft.com> wrote in message
> > news:77A25BF5-8193-4275-9D1E-209917DBB8D6@microsoft.com...
> > > I am using beforesaveas to prompt the save as screen with a
pre-defined
> > name.
> > > When using the save button everything seems to be functioning ok as I
am
> > > using Cancel = True. -When using the save as function I get the
> > pre-defined
> > > name, however after confirming, using the save button, the screen pops
up
> > a
> > > second time. As this file needs to be sent to end-users I don't want
this
> > to
> > > happen. What can be done to avoid this? Find below the subroutine.
> > >
> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> > > Cancel As Boolean)
> > > NameToSave = Sheet15.Cells(5, 2) & " - CDP - " & Sheet15.Cells(3,
2)
> > &
> > > " (v" & Year(Date) & Month(Date) & Day(Date) & ")"
> > > If Sheet15.Cells(3, 2) = "" Then
> > > Answer = MsgBox("Before being able to save this file you need
to
> > > select a year in the parameter sheet.", Buttons:=48)
> > > Cancel = True
> > > Sheet15.Activate
> > > Exit Sub
> > > End If
> > >
> > > If Sheet15.Cells(5, 2) = "" Then
> > > Answer = MsgBox("Before being able to save this file you need
to
> > > select a country in the parameter sheet.", Buttons:=48)
> > > Cancel = True
> > > Sheet15.Activate
> > > Exit Sub
> > > End If
> > >
> > > If SaveAsUI = False Then
> > > If MsgBox("Do you really want to save this Workbook? ",
vbYesNo) =
> > > vbNo Then
> > > Cancel = True
> > > Exit Sub
> > > Else
> > > Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> > > Cancel = True
> > > Exit Sub
> > > End If
> > > Else
> > > Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> > > End If
> > >
> > > End Sub
> >
> >
> >

Bart V · 07-19-2006, 04:35 AM

Nick,

I have done this, but it de-activates both the save as and the save buttons.
So no save at all happens.

Bart V

"NickHK" wrote:

> Bart,
> Look at your code:
> If SaveAsUI = False Then
> If MsgBox("Do you really want to save this Workbook? ", vbYesNo) =
> vbNo Then
> Cancel = True
> Exit Sub
> Else
> Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> Cancel = True
> Exit Sub
> End If
> Else
> '************* This is where you will perform the Save
> Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> '************ There is no Cancel=True
> End If
>
>
> NickHK
>
>
> "Bart V" <BartV@discussions.microsoft.com> wrote in message
> news:C02E12FD-DFBC-427B-AB52-0A0484D74452@microsoft.com...
> > Nick,
> >
> > I tried this already, but it did not work. I guess the problem is that I
> use
> > the save as button. With the save button everything works fine.
> >
> > Bart V
> >
> > "NickHK" wrote:
> >
> > > Bart,
> > > Assuming everything is successful, the file is saved through your last
> line
> > > of code.
> > > However, you are in the _BeforeSave event and you have not cancelled the
> > > reason that event was called.
> > > So, add a Cancel=True
> > >
> > > Also, do you all the SaveAsUI checks at all ?
> > >
> > > NickHK
> > >
> > > "Bart V" <BartV@discussions.microsoft.com> wrote in message
> > > news:77A25BF5-8193-4275-9D1E-209917DBB8D6@microsoft.com...
> > > > I am using beforesaveas to prompt the save as screen with a
> pre-defined
> > > name.
> > > > When using the save button everything seems to be functioning ok as I
> am
> > > > using Cancel = True. -When using the save as function I get the
> > > pre-defined
> > > > name, however after confirming, using the save button, the screen pops
> up
> > > a
> > > > second time. As this file needs to be sent to end-users I don't want
> this
> > > to
> > > > happen. What can be done to avoid this? Find below the subroutine.
> > > >
> > > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> > > > Cancel As Boolean)
> > > > NameToSave = Sheet15.Cells(5, 2) & " - CDP - " & Sheet15.Cells(3,
> 2)
> > > &
> > > > " (v" & Year(Date) & Month(Date) & Day(Date) & ")"
> > > > If Sheet15.Cells(3, 2) = "" Then
> > > > Answer = MsgBox("Before being able to save this file you need
> to
> > > > select a year in the parameter sheet.", Buttons:=48)
> > > > Cancel = True
> > > > Sheet15.Activate
> > > > Exit Sub
> > > > End If
> > > >
> > > > If Sheet15.Cells(5, 2) = "" Then
> > > > Answer = MsgBox("Before being able to save this file you need
> to
> > > > select a country in the parameter sheet.", Buttons:=48)
> > > > Cancel = True
> > > > Sheet15.Activate
> > > > Exit Sub
> > > > End If
> > > >
> > > > If SaveAsUI = False Then
> > > > If MsgBox("Do you really want to save this Workbook? ",
> vbYesNo) =
> > > > vbNo Then
> > > > Cancel = True
> > > > Exit Sub
> > > > Else
> > > > Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> > > > Cancel = True
> > > > Exit Sub
> > > > End If
> > > > Else
> > > > Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> > > > End If
> > > >
> > > > End Sub
> > >
> > >
> > >
>
>
>

NickHK · 07-19-2006, 05:30 AM

Bart,
Personally, I never use the dialog like this. I find it more reliable:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim Retval As Variant
With Application
Retval = .GetSaveAsFilename()
If Retval <> False Then
.EnableEvents = False
ThisWorkbook.SaveAs Retval
.EnableEvents = True
End If
End With
Cancel = True
End Sub

NickHK

"Bart V" <BartV@discussions.microsoft.com> wrote in message
news:A541C098-87C1-4556-A749-A8A60511A921@microsoft.com...
> Nick,
>
> I have done this, but it de-activates both the save as and the save
buttons.
> So no save at all happens.
>
> Bart V
>
> "NickHK" wrote:
>
> > Bart,
> > Look at your code:
> > If SaveAsUI = False Then
> > If MsgBox("Do you really want to save this Workbook? ",
vbYesNo) =
> > vbNo Then
> > Cancel = True
> > Exit Sub
> > Else
> > Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> > Cancel = True
> > Exit Sub
> > End If
> > Else
> > '************* This is where you will perform the Save
> > Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> > '************ There is no Cancel=True
> > End If
> >
> >
> > NickHK
> >
> >
> > "Bart V" <BartV@discussions.microsoft.com> wrote in message
> > news:C02E12FD-DFBC-427B-AB52-0A0484D74452@microsoft.com...
> > > Nick,
> > >
> > > I tried this already, but it did not work. I guess the problem is that
I
> > use
> > > the save as button. With the save button everything works fine.
> > >
> > > Bart V
> > >
> > > "NickHK" wrote:
> > >
> > > > Bart,
> > > > Assuming everything is successful, the file is saved through your
last
> > line
> > > > of code.
> > > > However, you are in the _BeforeSave event and you have not cancelled
the
> > > > reason that event was called.
> > > > So, add a Cancel=True
> > > >
> > > > Also, do you all the SaveAsUI checks at all ?
> > > >
> > > > NickHK
> > > >
> > > > "Bart V" <BartV@discussions.microsoft.com> wrote in message
> > > > news:77A25BF5-8193-4275-9D1E-209917DBB8D6@microsoft.com...
> > > > > I am using beforesaveas to prompt the save as screen with a
> > pre-defined
> > > > name.
> > > > > When using the save button everything seems to be functioning ok
as I
> > am
> > > > > using Cancel = True. -When using the save as function I get the
> > > > pre-defined
> > > > > name, however after confirming, using the save button, the screen
pops
> > up
> > > > a
> > > > > second time. As this file needs to be sent to end-users I don't
want
> > this
> > > > to
> > > > > happen. What can be done to avoid this? Find below the subroutine.
> > > > >
> > > > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
> > > > > Cancel As Boolean)
> > > > > NameToSave = Sheet15.Cells(5, 2) & " - CDP - " &
Sheet15.Cells(3,
> > 2)
> > > > &
> > > > > " (v" & Year(Date) & Month(Date) & Day(Date) & ")"
> > > > > If Sheet15.Cells(3, 2) = "" Then
> > > > > Answer = MsgBox("Before being able to save this file you
need
> > to
> > > > > select a year in the parameter sheet.", Buttons:=48)
> > > > > Cancel = True
> > > > > Sheet15.Activate
> > > > > Exit Sub
> > > > > End If
> > > > >
> > > > > If Sheet15.Cells(5, 2) = "" Then
> > > > > Answer = MsgBox("Before being able to save this file you
need
> > to
> > > > > select a country in the parameter sheet.", Buttons:=48)
> > > > > Cancel = True
> > > > > Sheet15.Activate
> > > > > Exit Sub
> > > > > End If
> > > > >
> > > > > If SaveAsUI = False Then
> > > > > If MsgBox("Do you really want to save this Workbook? ",
> > vbYesNo) =
> > > > > vbNo Then
> > > > > Cancel = True
> > > > > Exit Sub
> > > > > Else
> > > > > Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> > > > > Cancel = True
> > > > > Exit Sub
> > > > > End If
> > > > > Else
> > > > > Application.Dialogs(xlDialogSaveAs).Show (NameToSave)
> > > > > End If
> > > > >
> > > > > End Sub
> > > >
> > > >
> > > >
> >
> >
> >