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

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" wrote in message
...
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 - I was missing the obvious

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

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

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

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" wrote in message
...
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" wrote in message
...
Thanks Rob - thanks for explaining it as well :-)

"Rob Bovey" wrote in message
...

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

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