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,
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

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

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

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

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

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