Prompting a predefined filename to be saved
 

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

Prompting a predefined filename to be saved
 

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" wrote in message
...
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

Prompting a predefined filename to be saved
 

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" wrote in message
...
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

Prompting a predefined filename to be saved
 

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" wrote in message
...
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" wrote in message
...
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

Prompting a predefined filename to be saved
 

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" wrote in message
...
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" wrote in message
...
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

Prompting a predefined filename to be saved
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Prompting a predefined filename to be saved
 

Nick,

Thanks for your help. I am just an occasional programmer using VB only when
I notice that excel cannot provide me with a solution.

Bart V

"NickHK" wrote:

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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