BeforeSave Problem

Hi All,

I am trying to create a beforeSave event so that when the user presses save,
an inputbox comes up and the user specifies part of the filename and then
the code automatically adds additional information to the filename. I have
two problems:-

1) My code causes excel to crash
2) The inputbox comes up twice for some reason

Any ideas:-

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim FilePath As String
Dim varInput As String

FilePath = "S:\SmartMarket\SMV Project Administration\Sign-Offs\Construct
Phase\"

On Error GoTo addError
varInput = InputBox("......\Sign-Offs\Construct Phase\SMV - Sign-Off for
XXXXX.xls", _
"Sign-off Sheet Description")

ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"

addError:

MsgBox "No value submitted - File Not Saved"

End Sub

Ta

Andi

hi,
have you tried to step throught the code to see what line is crashing the
macro?

FSt1

"Andibevan" wrote:

> Hi All,
>
> I am trying to create a beforeSave event so that when the user presses save,
> an inputbox comes up and the user specifies part of the filename and then
> the code automatically adds additional information to the filename. I have
> two problems:-
>
> 1) My code causes excel to crash
> 2) The inputbox comes up twice for some reason
>
> Any ideas:-
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
>
> Dim FilePath As String
> Dim varInput As String
>
> FilePath = "S:\SmartMarket\SMV Project Administration\Sign-Offs\Construct
> Phase\"
>
> On Error GoTo addError
> varInput = InputBox("......\Sign-Offs\Construct Phase\SMV - Sign-Off for
> XXXXX.xls", _
> "Sign-off Sheet Description")
>
> ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
>
> addError:
>
> MsgBox "No value submitted - File Not Saved"
>
> End Sub
>
> Ta
>
> Andi
>
>
>

How do you step through an event driven macro?

"FSt1" <[email protected]> wrote in message
news:[email protected]...
> hi,
> have you tried to step throught the code to see what line is crashing the
> macro?
>
> FSt1
>
> "Andibevan" wrote:
>
> > Hi All,
> >
> > I am trying to create a beforeSave event so that when the user presses
save,
> > an inputbox comes up and the user specifies part of the filename and
then
> > the code automatically adds additional information to the filename. I
have
> > two problems:-
> >
> > 1) My code causes excel to crash
> > 2) The inputbox comes up twice for some reason
> >
> > Any ideas:-
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> >
> > Dim FilePath As String
> > Dim varInput As String
> >
> > FilePath = "S:\SmartMarket\SMV Project
Administration\Sign-Offs\Construct
> > Phase\"
> >
> > On Error GoTo addError
> > varInput = InputBox("......\Sign-Offs\Construct Phase\SMV - Sign-Off
for
> > XXXXX.xls", _
> > "Sign-off Sheet Description")
> >
> > ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
> >
> > addError:
> >
> > MsgBox "No value submitted - File Not Saved"
> >
> > End Sub
> >
> > Ta
> >
> > Andi
> >
> >
> >

Because you are saving the workbook within the BeforeSave event you
cause the event to be fired a again. The reason Excel is crashing is because
you are not telling the BeforeSave event to stop Excel from trying to save
the file by setting the Cancel argument to True. To avoid these problems,
rewrite your event procedure like so:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim FilePath As String
Dim varInput As String

On Error GoTo ErrorHandler

''' This disables all Excel events.
Application.EnableEvents = False

''' The reason Excel was crashing was because you didn't
''' disable the default behaviour of the even like so:
Cancel = True

FilePath = "S:\SmartMarket\SMV Project
Administration\Sign-Offs\Construct Phase\"

varInput = InputBox("......\Sign-Offs\Construct Phase\SMV - Sign-Off for
XXXXX.xls", _
"Sign-off Sheet Description")

ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"

ErrorExit:
''' This makes sure events get turned back on again no matter what.
Application.EnableEvents = True

ErrorHandler:
MsgBox "No value submitted - File Not Saved"
Resume ErrorExit
End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Andibevan" <[email protected]> wrote in message
news:OW%[email protected]...
> Hi All,
>
> I am trying to create a beforeSave event so that when the user presses
> save,
> an inputbox comes up and the user specifies part of the filename and then
> the code automatically adds additional information to the filename. I
> have
> two problems:-
>
> 1) My code causes excel to crash
> 2) The inputbox comes up twice for some reason
>
> Any ideas:-
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
>
> Dim FilePath As String
> Dim varInput As String
>
> FilePath = "S:\SmartMarket\SMV Project Administration\Sign-Offs\Construct
> Phase\"
>
> On Error GoTo addError
> varInput = InputBox("......\Sign-Offs\Construct Phase\SMV - Sign-Off
> for
> XXXXX.xls", _
> "Sign-off Sheet Description")
>
> ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
>
> addError:
>
> MsgBox "No value submitted - File Not Saved"
>
> End Sub
>
> Ta
>
> Andi
>
>

hi,
rob answered your question. i was suspecting something along the cancel
event but was not sure.
but to answer your question.
open the vb editor. display the macro int the vb window.
click the left margin. a redish brown dot will appear. then click the start
macro icon. looks lilke a arrow head pointing right. the macro will run to
the dot and stop. by pressing F8, the macro will execute the code 1 line at a
time. this is a good de-bug tool.

regards
FSt1

"Andibevan" wrote:

> How do you step through an event driven macro?
>
> "FSt1" <[email protected]> wrote in message
> news:[email protected]...
> > hi,
> > have you tried to step throught the code to see what line is crashing the
> > macro?
> >
> > FSt1
> >
> > "Andibevan" wrote:
> >
> > > Hi All,
> > >
> > > I am trying to create a beforeSave event so that when the user presses
> save,
> > > an inputbox comes up and the user specifies part of the filename and
> then
> > > the code automatically adds additional information to the filename. I
> have
> > > two problems:-
> > >
> > > 1) My code causes excel to crash
> > > 2) The inputbox comes up twice for some reason
> > >
> > > Any ideas:-
> > >
> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > Boolean)
> > >
> > > Dim FilePath As String
> > > Dim varInput As String
> > >
> > > FilePath = "S:\SmartMarket\SMV Project
> Administration\Sign-Offs\Construct
> > > Phase\"
> > >
> > > On Error GoTo addError
> > > varInput = InputBox("......\Sign-Offs\Construct Phase\SMV - Sign-Off
> for
> > > XXXXX.xls", _
> > > "Sign-off Sheet Description")
> > >
> > > ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
> > >
> > > addError:
> > >
> > > MsgBox "No value submitted - File Not Saved"
> > >
> > > End Sub
> > >
> > > Ta
> > >
> > > Andi
> > >
> > >
> > >
>
>
>

Looks like I forgot an Exit Sub in the example I posted. The end of the
event procedure should look like this:

ErrorExit:
''' This makes sure events get turned back on again no matter what.
Application.EnableEvents = True

Exit Sub

ErrorHandler:
MsgBox "No value submitted - File Not Saved"
Resume ErrorExit
End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Rob Bovey" <[email protected]> wrote in message
news:[email protected]...
>
> Because you are saving the workbook within the BeforeSave event you
> cause the event to be fired a again. The reason Excel is crashing is
> because you are not telling the BeforeSave event to stop Excel from trying
> to save the file by setting the Cancel argument to True. To avoid these
> problems, rewrite your event procedure like so:
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
>
> Dim FilePath As String
> Dim varInput As String
>
> On Error GoTo ErrorHandler
>
> ''' This disables all Excel events.
> Application.EnableEvents = False
>
> ''' The reason Excel was crashing was because you didn't
> ''' disable the default behaviour of the even like so:
> Cancel = True
>
> FilePath = "S:\SmartMarket\SMV Project
> Administration\Sign-Offs\Construct Phase\"
>
> varInput = InputBox("......\Sign-Offs\Construct Phase\SMV - Sign-Off
> for XXXXX.xls", _
> "Sign-off Sheet Description")
>
> ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
>
> ErrorExit:
> ''' This makes sure events get turned back on again no matter what.
> Application.EnableEvents = True
>
> ErrorHandler:
> MsgBox "No value submitted - File Not Saved"
> Resume ErrorExit
> End Sub
>
> --
> Rob Bovey, Excel MVP
> Application Professionals
> http://www.appspro.com/
>
> * Take your Excel development skills to the next level.
> * Professional Excel Development
> http://www.appspro.com/Books/Books.htm
>
> "Andibevan" <[email protected]> wrote in message
> news:OW%[email protected]...
>> Hi All,
>>
>> I am trying to create a beforeSave event so that when the user presses
>> save,
>> an inputbox comes up and the user specifies part of the filename and then
>> the code automatically adds additional information to the filename. I
>> have
>> two problems:-
>>
>> 1) My code causes excel to crash
>> 2) The inputbox comes up twice for some reason
>>
>> Any ideas:-
>>
>> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
>> Boolean)
>>
>> Dim FilePath As String
>> Dim varInput As String
>>
>> FilePath = "S:\SmartMarket\SMV Project Administration\Sign-Offs\Construct
>> Phase\"
>>
>> On Error GoTo addError
>> varInput = InputBox("......\Sign-Offs\Construct Phase\SMV - Sign-Off
>> for
>> XXXXX.xls", _
>> "Sign-off Sheet Description")
>>
>> ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
>>
>> addError:
>>
>> MsgBox "No value submitted - File Not Saved"
>>
>> End Sub
>>
>> Ta
>>
>> Andi
>>
>>
>
>

Thanks Rob - thanks for explaining it as well :-)

"Rob Bovey" <[email protected]> wrote in message
news:%[email protected]...
>
> Looks like I forgot an Exit Sub in the example I posted. The end of
the
> event procedure should look like this:
>
> ErrorExit:
> ''' This makes sure events get turned back on again no matter what.
> Application.EnableEvents = True
>
> Exit Sub
>
> ErrorHandler:
> MsgBox "No value submitted - File Not Saved"
> Resume ErrorExit
> End Sub
>
> --
> Rob Bovey, Excel MVP
> Application Professionals
> http://www.appspro.com/
>
> * Take your Excel development skills to the next level.
> * Professional Excel Development
> http://www.appspro.com/Books/Books.htm
>
> "Rob Bovey" <[email protected]> wrote in message
> news:[email protected]...
> >
> > Because you are saving the workbook within the BeforeSave event you
> > cause the event to be fired a again. The reason Excel is crashing is
> > because you are not telling the BeforeSave event to stop Excel from
trying
> > to save the file by setting the Cancel argument to True. To avoid these
> > problems, rewrite your event procedure like so:
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> >
> > Dim FilePath As String
> > Dim varInput As String
> >
> > On Error GoTo ErrorHandler
> >
> > ''' This disables all Excel events.
> > Application.EnableEvents = False
> >
> > ''' The reason Excel was crashing was because you didn't
> > ''' disable the default behaviour of the even like so:
> > Cancel = True
> >
> > FilePath = "S:\SmartMarket\SMV Project
> > Administration\Sign-Offs\Construct Phase\"
> >
> > varInput = InputBox("......\Sign-Offs\Construct Phase\SMV - Sign-Off
> > for XXXXX.xls", _
> > "Sign-off Sheet Description")
> >
> > ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
> >
> > ErrorExit:
> > ''' This makes sure events get turned back on again no matter what.
> > Application.EnableEvents = True
> >
> > ErrorHandler:
> > MsgBox "No value submitted - File Not Saved"
> > Resume ErrorExit
> > End Sub
> >
> > --
> > Rob Bovey, Excel MVP
> > Application Professionals
> > http://www.appspro.com/
> >
> > * Take your Excel development skills to the next level.
> > * Professional Excel Development
> > http://www.appspro.com/Books/Books.htm
> >
> > "Andibevan" <[email protected]> wrote in message
> > news:OW%[email protected]...
> >> Hi All,
> >>
> >> I am trying to create a beforeSave event so that when the user presses
> >> save,
> >> an inputbox comes up and the user specifies part of the filename and
then
> >> the code automatically adds additional information to the filename. I
> >> have
> >> two problems:-
> >>
> >> 1) My code causes excel to crash
> >> 2) The inputbox comes up twice for some reason
> >>
> >> Any ideas:-
> >>
> >> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> >> Boolean)
> >>
> >> Dim FilePath As String
> >> Dim varInput As String
> >>
> >> FilePath = "S:\SmartMarket\SMV Project
Administration\Sign-Offs\Construct
> >> Phase\"
> >>
> >> On Error GoTo addError
> >> varInput = InputBox("......\Sign-Offs\Construct Phase\SMV - Sign-Off
> >> for
> >> XXXXX.xls", _
> >> "Sign-off Sheet Description")
> >>
> >> ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
> >>
> >> addError:
> >>
> >> MsgBox "No value submitted - File Not Saved"
> >>
> >> End Sub
> >>
> >> Ta
> >>
> >> Andi
> >>
> >>
> >
> >
>
>

Thanks - I was missing the obvious

"FSt1" <[email protected]> wrote in message
news:[email protected]...
> hi,
> rob answered your question. i was suspecting something along the cancel
> event but was not sure.
> but to answer your question.
> open the vb editor. display the macro int the vb window.
> click the left margin. a redish brown dot will appear. then click the
start
> macro icon. looks lilke a arrow head pointing right. the macro will run to
> the dot and stop. by pressing F8, the macro will execute the code 1 line
at a
> time. this is a good de-bug tool.
>
> regards
> FSt1
>
> "Andibevan" wrote:
>
> > How do you step through an event driven macro?
> >
> > "FSt1" <[email protected]> wrote in message
> > news:[email protected]...
> > > hi,
> > > have you tried to step throught the code to see what line is crashing
the
> > > macro?
> > >
> > > FSt1
> > >
> > > "Andibevan" wrote:
> > >
> > > > Hi All,
> > > >
> > > > I am trying to create a beforeSave event so that when the user
presses
> > save,
> > > > an inputbox comes up and the user specifies part of the filename and
> > then
> > > > the code automatically adds additional information to the filename.
I
> > have
> > > > two problems:-
> > > >
> > > > 1) My code causes excel to crash
> > > > 2) The inputbox comes up twice for some reason
> > > >
> > > > Any ideas:-
> > > >
> > > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > > Boolean)
> > > >
> > > > Dim FilePath As String
> > > > Dim varInput As String
> > > >
> > > > FilePath = "S:\SmartMarket\SMV Project
> > Administration\Sign-Offs\Construct
> > > > Phase\"
> > > >
> > > > On Error GoTo addError
> > > > varInput = InputBox("......\Sign-Offs\Construct Phase\SMV -
Sign-Off
> > for
> > > > XXXXX.xls", _
> > > > "Sign-off Sheet Description")
> > > >
> > > > ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
> > > >
> > > > addError:
> > > >
> > > > MsgBox "No value submitted - File Not Saved"
> > > >
> > > > End Sub
> > > >
> > > > Ta
> > > >
> > > > Andi
> > > >
> > > >
> > > >
> >
> >
> >

Thanks FSt1 - I was missing the obvious

"FSt1" <[email protected]> wrote in message
news:[email protected]...
> hi,
> rob answered your question. i was suspecting something along the cancel
> event but was not sure.
> but to answer your question.
> open the vb editor. display the macro int the vb window.
> click the left margin. a redish brown dot will appear. then click the
start
> macro icon. looks lilke a arrow head pointing right. the macro will run to
> the dot and stop. by pressing F8, the macro will execute the code 1 line
at a
> time. this is a good de-bug tool.
>
> regards
> FSt1
>
> "Andibevan" wrote:
>
> > How do you step through an event driven macro?
> >
> > "FSt1" <[email protected]> wrote in message
> > news:[email protected]...
> > > hi,
> > > have you tried to step throught the code to see what line is crashing
the
> > > macro?
> > >
> > > FSt1
> > >
> > > "Andibevan" wrote:
> > >
> > > > Hi All,
> > > >
> > > > I am trying to create a beforeSave event so that when the user
presses
> > save,
> > > > an inputbox comes up and the user specifies part of the filename and
> > then
> > > > the code automatically adds additional information to the filename.
I
> > have
> > > > two problems:-
> > > >
> > > > 1) My code causes excel to crash
> > > > 2) The inputbox comes up twice for some reason
> > > >
> > > > Any ideas:-
> > > >
> > > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > > Boolean)
> > > >
> > > > Dim FilePath As String
> > > > Dim varInput As String
> > > >
> > > > FilePath = "S:\SmartMarket\SMV Project
> > Administration\Sign-Offs\Construct
> > > > Phase\"
> > > >
> > > > On Error GoTo addError
> > > > varInput = InputBox("......\Sign-Offs\Construct Phase\SMV -
Sign-Off
> > for
> > > > XXXXX.xls", _
> > > > "Sign-off Sheet Description")
> > > >
> > > > ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
> > > >
> > > > addError:
> > > >
> > > > MsgBox "No value submitted - File Not Saved"
> > > >
> > > > End Sub
> > > >
> > > > Ta
> > > >
> > > > Andi
> > > >
> > > >
> > > >
> >
> >
> >

This does create a problem when trying to save it as a template file as the
code forces the file to be saved as an xls.

The only solution I can see is to then rename the file to the template..

"Andibevan" <[email protected]> wrote in message
news:%[email protected]...
> Thanks Rob - thanks for explaining it as well :-)
>
> "Rob Bovey" <[email protected]> wrote in message
> news:%[email protected]...
> >
> > Looks like I forgot an Exit Sub in the example I posted. The end of
> the
> > event procedure should look like this:
> >
> > ErrorExit:
> > ''' This makes sure events get turned back on again no matter what.
> > Application.EnableEvents = True
> >
> > Exit Sub
> >
> > ErrorHandler:
> > MsgBox "No value submitted - File Not Saved"
> > Resume ErrorExit
> > End Sub
> >
> > --
> > Rob Bovey, Excel MVP
> > Application Professionals
> > http://www.appspro.com/
> >
> > * Take your Excel development skills to the next level.
> > * Professional Excel Development
> > http://www.appspro.com/Books/Books.htm
> >
> > "Rob Bovey" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > Because you are saving the workbook within the BeforeSave event you
> > > cause the event to be fired a again. The reason Excel is crashing is
> > > because you are not telling the BeforeSave event to stop Excel from
> trying
> > > to save the file by setting the Cancel argument to True. To avoid
these
> > > problems, rewrite your event procedure like so:
> > >
> > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > > Boolean)
> > >
> > > Dim FilePath As String
> > > Dim varInput As String
> > >
> > > On Error GoTo ErrorHandler
> > >
> > > ''' This disables all Excel events.
> > > Application.EnableEvents = False
> > >
> > > ''' The reason Excel was crashing was because you didn't
> > > ''' disable the default behaviour of the even like so:
> > > Cancel = True
> > >
> > > FilePath = "S:\SmartMarket\SMV Project
> > > Administration\Sign-Offs\Construct Phase\"
> > >
> > > varInput = InputBox("......\Sign-Offs\Construct Phase\SMV -
Sign-Off
> > > for XXXXX.xls", _
> > > "Sign-off Sheet Description")
> > >
> > > ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
> > >
> > > ErrorExit:
> > > ''' This makes sure events get turned back on again no matter what.
> > > Application.EnableEvents = True
> > >
> > > ErrorHandler:
> > > MsgBox "No value submitted - File Not Saved"
> > > Resume ErrorExit
> > > End Sub
> > >
> > > --
> > > Rob Bovey, Excel MVP
> > > Application Professionals
> > > http://www.appspro.com/
> > >
> > > * Take your Excel development skills to the next level.
> > > * Professional Excel Development
> > > http://www.appspro.com/Books/Books.htm
> > >
> > > "Andibevan" <[email protected]> wrote in message
> > > news:OW%[email protected]...
> > >> Hi All,
> > >>
> > >> I am trying to create a beforeSave event so that when the user
presses
> > >> save,
> > >> an inputbox comes up and the user specifies part of the filename and
> then
> > >> the code automatically adds additional information to the filename.
I
> > >> have
> > >> two problems:-
> > >>
> > >> 1) My code causes excel to crash
> > >> 2) The inputbox comes up twice for some reason
> > >>
> > >> Any ideas:-
> > >>
> > >> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > >> Boolean)
> > >>
> > >> Dim FilePath As String
> > >> Dim varInput As String
> > >>
> > >> FilePath = "S:\SmartMarket\SMV Project
> Administration\Sign-Offs\Construct
> > >> Phase\"
> > >>
> > >> On Error GoTo addError
> > >> varInput = InputBox("......\Sign-Offs\Construct Phase\SMV -
Sign-Off
> > >> for
> > >> XXXXX.xls", _
> > >> "Sign-off Sheet Description")
> > >>
> > >> ActiveWorkbook.SaveAs Filename:=FilePath & varInput & ".xls"
> > >>
> > >> addError:
> > >>
> > >> MsgBox "No value submitted - File Not Saved"
> > >>
> > >> End Sub
> > >>
> > >> Ta
> > >>
> > >> Andi
> > >>
> > >>
> > >
> > >
> >
> >
>
>

"Andibevan" <[email protected]> wrote in message
news:[email protected]...
> This does create a problem when trying to save it as a template file as
> the
> code forces the file to be saved as an xls.
>
> The only solution I can see is to then rename the file to the template..

You can save the workbook as a template by changing your SaveAs line to
the following:

ActiveWorkbook.SaveAs _
Filename:=FilePath & varInput & ".xlt", _
FileFormat:=xlTemplate

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Hi Rob - I don't think I was clear enough, what I mean is I want to save an
excel template that forces the user to save an xls file with certain
specified bits of the file name.

If I change the line to what is below when the somebody uses the template it
will keep creating more template files, rather than xls files. Sorry it is
a bit difficult to explain, but if you try and create an .xlt file with the
code you originally gave me you will see the problem.

Thanks

Andi

"Rob Bovey" <[email protected]> wrote in message
news:[email protected]...
> "Andibevan" <[email protected]> wrote in message
> news:[email protected]...
> > This does create a problem when trying to save it as a template file as
> > the
> > code forces the file to be saved as an xls.
> >
> > The only solution I can see is to then rename the file to the template..
>
> You can save the workbook as a template by changing your SaveAs line
to
> the following:
>
> ActiveWorkbook.SaveAs _
> Filename:=FilePath & varInput & ".xlt", _
> FileFormat:=xlTemplate
>
> --
> Rob Bovey, Excel MVP
> Application Professionals
> http://www.appspro.com/
>
> * Take your Excel development skills to the next level.
> * Professional Excel Development
> http://www.appspro.com/Books/Books.htm
>
>

Hi Andi,

I guess I don't understand what you mean. I just created a template with
the BeforeSave event procedure in it and it seems to work fine here. What is
it about the template that doesn't work for you?

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Andibevan" <[email protected]> wrote in message
news:OW1Z%[email protected]...
> Hi Rob - I don't think I was clear enough, what I mean is I want to save
> an
> excel template that forces the user to save an xls file with certain
> specified bits of the file name.
>
> If I change the line to what is below when the somebody uses the template
> it
> will keep creating more template files, rather than xls files. Sorry it
> is
> a bit difficult to explain, but if you try and create an .xlt file with
> the
> code you originally gave me you will see the problem.
>
> Thanks
>
> Andi
>
> "Rob Bovey" <[email protected]> wrote in message
> news:[email protected]...
>> "Andibevan" <[email protected]> wrote in message
>> news:[email protected]...
>> > This does create a problem when trying to save it as a template file as
>> > the
>> > code forces the file to be saved as an xls.
>> >
>> > The only solution I can see is to then rename the file to the
>> > template..
>>
>> You can save the workbook as a template by changing your SaveAs line
> to
>> the following:
>>
>> ActiveWorkbook.SaveAs _
>> Filename:=FilePath & varInput & ".xlt", _
>> FileFormat:=xlTemplate
>>
>> --
>> Rob Bovey, Excel MVP
>> Application Professionals
>> http://www.appspro.com/
>>
>> * Take your Excel development skills to the next level.
>> * Professional Excel Development
>> http://www.appspro.com/Books/Books.htm
>>
>>
>
>

I'll explain differently.

If I start with a new document and insert the some code against the
beforesave event so that it automatically adds the .xls onto the name of the
file being saved.

When I come to save the document it automatically adds the .xls extention
due to the beforesave event. It means that you can only save the document
with a .xls extension which is a problem if you are trying to create an .xlt
file?



"Rob Bovey" <[email protected]> wrote in message
news:[email protected]...
> Hi Andi,
>
> I guess I don't understand what you mean. I just created a template
> with the BeforeSave event procedure in it and it seems to work fine here.
> What is it about the template that doesn't work for you?
>
> --
> Rob Bovey, Excel MVP
> Application Professionals
> http://www.appspro.com/
>
> * Take your Excel development skills to the next level.
> * Professional Excel Development
> http://www.appspro.com/Books/Books.htm
>
> "Andibevan" <[email protected]> wrote in message
> news:OW1Z%[email protected]...
>> Hi Rob - I don't think I was clear enough, what I mean is I want to save
>> an
>> excel template that forces the user to save an xls file with certain
>> specified bits of the file name.
>>
>> If I change the line to what is below when the somebody uses the template
>> it
>> will keep creating more template files, rather than xls files. Sorry it
>> is
>> a bit difficult to explain, but if you try and create an .xlt file with
>> the
>> code you originally gave me you will see the problem.
>>
>> Thanks
>>
>> Andi
>>
>> "Rob Bovey" <[email protected]> wrote in message
>> news:[email protected]...
>>> "Andibevan" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > This does create a problem when trying to save it as a template file
>>> > as
>>> > the
>>> > code forces the file to be saved as an xls.
>>> >
>>> > The only solution I can see is to then rename the file to the
>>> > template..
>>>
>>> You can save the workbook as a template by changing your SaveAs line
>> to
>>> the following:
>>>
>>> ActiveWorkbook.SaveAs _
>>> Filename:=FilePath & varInput & ".xlt", _
>>> FileFormat:=xlTemplate
>>>
>>> --
>>> Rob Bovey, Excel MVP
>>> Application Professionals
>>> http://www.appspro.com/
>>>
>>> * Take your Excel development skills to the next level.
>>> * Professional Excel Development
>>> http://www.appspro.com/Books/Books.htm
>>>
>>>
>>
>>
>
>

Hi Andi,

In that case you just need to add some extra code to the event procedure
that asks the user whether they want to create a workbook or a template.
Something like this:

Dim lAnswer As Long
Dim szMsg As String

''''' Other code here

szMsg = "Choose one of the following:" & vbLf & _
"Yes = Save as workbook" & vbLf & _
"No = Save as template"
lAnswer = MsgBox(szMsg, vbYesNo, "Save As What?")

If lAnswer = vbYes Then
''' Save as xls here.
Else
''' Save as xlt here.
End If

''''' Other code here

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Andibevan" <[email protected]> wrote in message
news:[email protected]...
> I'll explain differently.
>
> If I start with a new document and insert the some code against the
> beforesave event so that it automatically adds the .xls onto the name of
> the file being saved.
>
> When I come to save the document it automatically adds the .xls extention
> due to the beforesave event. It means that you can only save the document
> with a .xls extension which is a problem if you are trying to create an
> .xlt file?
>
>
>
> "Rob Bovey" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Andi,
>>
>> I guess I don't understand what you mean. I just created a template
>> with the BeforeSave event procedure in it and it seems to work fine here.
>> What is it about the template that doesn't work for you?
>>
>> --
>> Rob Bovey, Excel MVP
>> Application Professionals
>> http://www.appspro.com/
>>
>> * Take your Excel development skills to the next level.
>> * Professional Excel Development
>> http://www.appspro.com/Books/Books.htm
>>
>> "Andibevan" <[email protected]> wrote in message
>> news:OW1Z%[email protected]...
>>> Hi Rob - I don't think I was clear enough, what I mean is I want to save
>>> an
>>> excel template that forces the user to save an xls file with certain
>>> specified bits of the file name.
>>>
>>> If I change the line to what is below when the somebody uses the
>>> template it
>>> will keep creating more template files, rather than xls files. Sorry it
>>> is
>>> a bit difficult to explain, but if you try and create an .xlt file with
>>> the
>>> code you originally gave me you will see the problem.
>>>
>>> Thanks
>>>
>>> Andi
>>>
>>> "Rob Bovey" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> "Andibevan" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>> > This does create a problem when trying to save it as a template file
>>>> > as
>>>> > the
>>>> > code forces the file to be saved as an xls.
>>>> >
>>>> > The only solution I can see is to then rename the file to the
>>>> > template..
>>>>
>>>> You can save the workbook as a template by changing your SaveAs
>>>> line
>>> to
>>>> the following:
>>>>
>>>> ActiveWorkbook.SaveAs _
>>>> Filename:=FilePath & varInput & ".xlt", _
>>>> FileFormat:=xlTemplate
>>>>
>>>> --
>>>> Rob Bovey, Excel MVP
>>>> Application Professionals
>>>> http://www.appspro.com/
>>>>
>>>> * Take your Excel development skills to the next level.
>>>> * Professional Excel Development
>>>> http://www.appspro.com/Books/Books.htm
>>>>
>>>>
>>>
>>>
>>
>>
>
>