Workbook_BeforeSave question

Does it make sense to force a SAVEAS during the workbook_beforesave event?
We have a "template" (not in the Excel terminology) that we don't want
overwritten. Is there a recommended way to ensure that doesn't happen with
the Workbook_beforesave or another method?

Thanks,

Hi Barb

You can use this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
'do nothing
Else
Cancel = True
End If
End Sub

But if you use save as to another name the code is also working
Maybe add a check for the template name


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> Does it make sense to force a SAVEAS during the workbook_beforesave event?
> We have a "template" (not in the Excel terminology) that we don't want
> overwritten. Is there a recommended way to ensure that doesn't happen with
> the Workbook_beforesave or another method?
>
> Thanks,
>

Make the file ReadOnly by changing it's properties in Windows Explorer.



"Barb Reinhardt" <[email protected]> schreef in
bericht news:[email protected]...
> Does it make sense to force a SAVEAS during the workbook_beforesave event?
> We have a "template" (not in the Excel terminology) that we don't want
> overwritten. Is there a recommended way to ensure that doesn't happen
> with
> the Workbook_beforesave or another method?
>
> Thanks,
>

Yes it can do. If you notice this event procedure has a SaveAsUI argument,
which tells you if it is being saved as (True) or not (False). You can test
this and if false set Cancel to True and do a SaveAs (disable events first
though).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> Does it make sense to force a SAVEAS during the workbook_beforesave event?
> We have a "template" (not in the Excel terminology) that we don't want
> overwritten. Is there a recommended way to ensure that doesn't happen
with
> the Workbook_beforesave or another method?
>
> Thanks,
>

If I check the ActiveWorkbook.name against the predefined name of the
template and it matches, how do I force a SaveAs?

"Ron de Bruin" wrote:

> Hi Barb
>
> You can use this
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
> If SaveAsUI Then
> 'do nothing
> Else
> Cancel = True
> End If
> End Sub
>
> But if you use save as to another name the code is also working
> Maybe add a check for the template name
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Barb Reinhardt" <[email protected]> wrote in message
> news:[email protected]...
> > Does it make sense to force a SAVEAS during the workbook_beforesave event?
> > We have a "template" (not in the Excel terminology) that we don't want
> > overwritten. Is there a recommended way to ensure that doesn't happen with
> > the Workbook_beforesave or another method?
> >
> > Thanks,
> >
>
>
>

Maybe this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.Name <> "template.xls" Then Exit Sub
If SaveAsUI Then
'do nothing
Else
Cancel = True
End If
End Sub

Sub Savethetemplate()
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> If I check the ActiveWorkbook.name against the predefined name of the
> template and it matches, how do I force a SaveAs?
>
> "Ron de Bruin" wrote:
>
>> Hi Barb
>>
>> You can use this
>>
>> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
>> If SaveAsUI Then
>> 'do nothing
>> Else
>> Cancel = True
>> End If
>> End Sub
>>
>> But if you use save as to another name the code is also working
>> Maybe add a check for the template name
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Barb Reinhardt" <[email protected]> wrote in message
>> news:[email protected]...
>> > Does it make sense to force a SAVEAS during the workbook_beforesave event?
>> > We have a "template" (not in the Excel terminology) that we don't want
>> > overwritten. Is there a recommended way to ensure that doesn't happen with
>> > the Workbook_beforesave or another method?
>> >
>> > Thanks,
>> >
>>
>>
>>

Just some thoughts:

Even if the SaveAsUI is true, the default file name will be this file's name
and allowing the user to select a name will not prevent them from overwriting
the file (they can select the same name).

Disabling macros disables any programmed protection.

Nothing will prevent the user from copying over another file over this one
using the operating system.


Ron,
what is this line supposed to do?
If ThisWorkbook.Name <> "template.xls" Then Exit Sub

it basically says if the name is not template.xls, then you can't save.
that didn't sound like anything the OP wanted.

In any event, I would think you would have to cancel the save triggering the
event, then use getsaveasfilename to let the user pick a name, then determine
if the name is acceptable. If it is, save. If it isn't prompt again or exit
the routine.

--
Regards,
Tom Ogilvy



"Ron de Bruin" wrote:

> Maybe this
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
> If ThisWorkbook.Name <> "template.xls" Then Exit Sub
> If SaveAsUI Then
> 'do nothing
> Else
> Cancel = True
> End If
> End Sub
>
> Sub Savethetemplate()
> Application.EnableEvents = False
> ThisWorkbook.Save
> Application.EnableEvents = True
> End Sub
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Barb Reinhardt" <[email protected]> wrote in message
> news:[email protected]...
> > If I check the ActiveWorkbook.name against the predefined name of the
> > template and it matches, how do I force a SaveAs?
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi Barb
> >>
> >> You can use this
> >>
> >> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
> >> If SaveAsUI Then
> >> 'do nothing
> >> Else
> >> Cancel = True
> >> End If
> >> End Sub
> >>
> >> But if you use save as to another name the code is also working
> >> Maybe add a check for the template name
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Barb Reinhardt" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Does it make sense to force a SAVEAS during the workbook_beforesave event?
> >> > We have a "template" (not in the Excel terminology) that we don't want
> >> > overwritten. Is there a recommended way to ensure that doesn't happen with
> >> > the Workbook_beforesave or another method?
> >> >
> >> > Thanks,
> >> >
> >>
> >>
> >>
>
>
>

Hi Tom

As I understand it the OP want not to overwrite the original template.

If he Save as the template a different name he can save the file now because of this line
If ThisWorkbook.Name <> "template.xls" Then Exit Sub

But I agree with your thoughts
I will never use it

A real template is another option for the OP maybe


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tom Ogilvy" <[email protected]> wrote in message news:[email protected]...
> Just some thoughts:
>
> Even if the SaveAsUI is true, the default file name will be this file's name
> and allowing the user to select a name will not prevent them from overwriting
> the file (they can select the same name).
>
> Disabling macros disables any programmed protection.
>
> Nothing will prevent the user from copying over another file over this one
> using the operating system.
>
>
> Ron,
> what is this line supposed to do?
> If ThisWorkbook.Name <> "template.xls" Then Exit Sub
>
> it basically says if the name is not template.xls, then you can't save.
> that didn't sound like anything the OP wanted.
>
> In any event, I would think you would have to cancel the save triggering the
> event, then use getsaveasfilename to let the user pick a name, then determine
> if the name is acceptable. If it is, save. If it isn't prompt again or exit
> the routine.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Ron de Bruin" wrote:
>
>> Maybe this
>>
>> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
>> If ThisWorkbook.Name <> "template.xls" Then Exit Sub
>> If SaveAsUI Then
>> 'do nothing
>> Else
>> Cancel = True
>> End If
>> End Sub
>>
>> Sub Savethetemplate()
>> Application.EnableEvents = False
>> ThisWorkbook.Save
>> Application.EnableEvents = True
>> End Sub
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Barb Reinhardt" <[email protected]> wrote in message
>> news:[email protected]...
>> > If I check the ActiveWorkbook.name against the predefined name of the
>> > template and it matches, how do I force a SaveAs?
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi Barb
>> >>
>> >> You can use this
>> >>
>> >> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
>> >> If SaveAsUI Then
>> >> 'do nothing
>> >> Else
>> >> Cancel = True
>> >> End If
>> >> End Sub
>> >>
>> >> But if you use save as to another name the code is also working
>> >> Maybe add a check for the template name
>> >>
>> >>
>> >> --
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >>
>> >> "Barb Reinhardt" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Does it make sense to force a SAVEAS during the workbook_beforesave event?
>> >> > We have a "template" (not in the Excel terminology) that we don't want
>> >> > overwritten. Is there a recommended way to ensure that doesn't happen with
>> >> > the Workbook_beforesave or another method?
>> >> >
>> >> > Thanks,
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

I see what you are saying. Nonetheless, with that approach, the user can
still select the name template.xls and save the workbook.

--
Regards,
Tom Ogilvy




"Ron de Bruin" wrote:

> Hi Tom
>
> As I understand it the OP want not to overwrite the original template.
>
> If he Save as the template a different name he can save the file now because of this line
> If ThisWorkbook.Name <> "template.xls" Then Exit Sub
>
> But I agree with your thoughts
> I will never use it
>
> A real template is another option for the OP maybe
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Tom Ogilvy" <[email protected]> wrote in message news:[email protected]...
> > Just some thoughts:
> >
> > Even if the SaveAsUI is true, the default file name will be this file's name
> > and allowing the user to select a name will not prevent them from overwriting
> > the file (they can select the same name).
> >
> > Disabling macros disables any programmed protection.
> >
> > Nothing will prevent the user from copying over another file over this one
> > using the operating system.
> >
> >
> > Ron,
> > what is this line supposed to do?
> > If ThisWorkbook.Name <> "template.xls" Then Exit Sub
> >
> > it basically says if the name is not template.xls, then you can't save.
> > that didn't sound like anything the OP wanted.
> >
> > In any event, I would think you would have to cancel the save triggering the
> > event, then use getsaveasfilename to let the user pick a name, then determine
> > if the name is acceptable. If it is, save. If it isn't prompt again or exit
> > the routine.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Ron de Bruin" wrote:
> >
> >> Maybe this
> >>
> >> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
> >> If ThisWorkbook.Name <> "template.xls" Then Exit Sub
> >> If SaveAsUI Then
> >> 'do nothing
> >> Else
> >> Cancel = True
> >> End If
> >> End Sub
> >>
> >> Sub Savethetemplate()
> >> Application.EnableEvents = False
> >> ThisWorkbook.Save
> >> Application.EnableEvents = True
> >> End Sub
> >>
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Barb Reinhardt" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > If I check the ActiveWorkbook.name against the predefined name of the
> >> > template and it matches, how do I force a SaveAs?
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> Hi Barb
> >> >>
> >> >> You can use this
> >> >>
> >> >> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
> >> >> If SaveAsUI Then
> >> >> 'do nothing
> >> >> Else
> >> >> Cancel = True
> >> >> End If
> >> >> End Sub
> >> >>
> >> >> But if you use save as to another name the code is also working
> >> >> Maybe add a check for the template name
> >> >>
> >> >>
> >> >> --
> >> >> Regards Ron de Bruin
> >> >> http://www.rondebruin.nl
> >> >>
> >> >>
> >> >>
> >> >> "Barb Reinhardt" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Does it make sense to force a SAVEAS during the workbook_beforesave event?
> >> >> > We have a "template" (not in the Excel terminology) that we don't want
> >> >> > overwritten. Is there a recommended way to ensure that doesn't happen with
> >> >> > the Workbook_beforesave or another method?
> >> >> >
> >> >> > Thanks,
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Yes you are correct Tom

It is not a good way


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Tom Ogilvy" <[email protected]> wrote in message news:[email protected]...
>I see what you are saying. Nonetheless, with that approach, the user can
> still select the name template.xls and save the workbook.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Ron de Bruin" wrote:
>
>> Hi Tom
>>
>> As I understand it the OP want not to overwrite the original template.
>>
>> If he Save as the template a different name he can save the file now because of this line
>> If ThisWorkbook.Name <> "template.xls" Then Exit Sub
>>
>> But I agree with your thoughts
>> I will never use it
>>
>> A real template is another option for the OP maybe
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Tom Ogilvy" <[email protected]> wrote in message news:[email protected]...
>> > Just some thoughts:
>> >
>> > Even if the SaveAsUI is true, the default file name will be this file's name
>> > and allowing the user to select a name will not prevent them from overwriting
>> > the file (they can select the same name).
>> >
>> > Disabling macros disables any programmed protection.
>> >
>> > Nothing will prevent the user from copying over another file over this one
>> > using the operating system.
>> >
>> >
>> > Ron,
>> > what is this line supposed to do?
>> > If ThisWorkbook.Name <> "template.xls" Then Exit Sub
>> >
>> > it basically says if the name is not template.xls, then you can't save.
>> > that didn't sound like anything the OP wanted.
>> >
>> > In any event, I would think you would have to cancel the save triggering the
>> > event, then use getsaveasfilename to let the user pick a name, then determine
>> > if the name is acceptable. If it is, save. If it isn't prompt again or exit
>> > the routine.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Maybe this
>> >>
>> >> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
>> >> If ThisWorkbook.Name <> "template.xls" Then Exit Sub
>> >> If SaveAsUI Then
>> >> 'do nothing
>> >> Else
>> >> Cancel = True
>> >> End If
>> >> End Sub
>> >>
>> >> Sub Savethetemplate()
>> >> Application.EnableEvents = False
>> >> ThisWorkbook.Save
>> >> Application.EnableEvents = True
>> >> End Sub
>> >>
>> >>
>> >>
>> >> --
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >>
>> >>
>> >> "Barb Reinhardt" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > If I check the ActiveWorkbook.name against the predefined name of the
>> >> > template and it matches, how do I force a SaveAs?
>> >> >
>> >> > "Ron de Bruin" wrote:
>> >> >
>> >> >> Hi Barb
>> >> >>
>> >> >> You can use this
>> >> >>
>> >> >> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
>> >> >> If SaveAsUI Then
>> >> >> 'do nothing
>> >> >> Else
>> >> >> Cancel = True
>> >> >> End If
>> >> >> End Sub
>> >> >>
>> >> >> But if you use save as to another name the code is also working
>> >> >> Maybe add a check for the template name
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Regards Ron de Bruin
>> >> >> http://www.rondebruin.nl
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Barb Reinhardt" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > Does it make sense to force a SAVEAS during the workbook_beforesave event?
>> >> >> > We have a "template" (not in the Excel terminology) that we don't want
>> >> >> > overwritten. Is there a recommended way to ensure that doesn't happen with
>> >> >> > the Workbook_beforesave or another method?
>> >> >> >
>> >> >> > Thanks,
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>