I have a set of data sums which are updated from anther excel document daily
and I need to be able to record these figures to be able to work out how much
work has been done between yesterday and today.
Since this document will be a template for other users I would like to be
able to automatically run a macro on shutdown to copy the values from today
to another position so as to be able to use them tomorrow. The macro is
simple enough. My problem is how do I make it run with no input from the
user? But still give the user time to print a report prior to the macro
running.
--
Jimmy

hi
you could put the code in the ThisWorkbook Before close event. or even the
before save event.
open the vb editor (alt + F11). click your VBAProject(workbook). click This
Workbook. in the left dropdown over the code window, chick This Workbook. in
the right dropdown, scroll to before close. type or paste your code.

Regards,
FSt1

"Jimmy D" wrote:

I have a set of data sums which are updated from anther excel document daily
and I need to be able to record these figures to be able to work out how much
work has been done between yesterday and today.
Since this document will be a template for other users I would like to be
able to automatically run a macro on shutdown to copy the values from today
to another position so as to be able to use them tomorrow. The macro is
simple enough. My problem is how do I make it run with no input from the
user? But still give the user time to print a report prior to the macro
running.
--
Jimmy

I don't know exactly what you mean by "give the user time to print"
but if you want to run a macro before closing go to your workbook
in the vba editor and write following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'here is your macro
End Sub

if you want to cancel the closing you can use
Cancel = true

hth

Carlo

On Nov 22, 9:05 am, Jimmy D wrote:
I have a set of data sums which are updated from anther excel document daily
and I need to be able to record these figures to be able to work out how much
work has been done between yesterday and today.
Since this document will be a template for other users I would like to be
able to automatically run a macro on shutdown to copy the values from today
to another position so as to be able to use them tomorrow. The macro is
simple enough. My problem is how do I make it run with no input from the
user? But still give the user time to print a report prior to the macro
running.
--
Jimmy

Unsure of what you are trying to do. Have tried to follow in vb but am not
able to work out where you are. I am working with excel 97 (possibly a bit of
a dinosaur) but is all I have.
Do I maybe have to start a new vb macro to do this? or can it be done with
the existing macro?
My left drop down only contains (General) and the right has the macro name
and (Declarations)
--
Jimmy


"FSt1" wrote:

hi
you could put the code in the ThisWorkbook Before close event. or even the
before save event.
open the vb editor (alt + F11). click your VBAProject(workbook). click This
Workbook. in the left dropdown over the code window, chick This Workbook. in
the right dropdown, scroll to before close. type or paste your code.

Regards,
FSt1

"Jimmy D" wrote:

I have a set of data sums which are updated from anther excel document daily
and I need to be able to record these figures to be able to work out how much
work has been done between yesterday and today.
Since this document will be a template for other users I would like to be
able to automatically run a macro on shutdown to copy the values from today
to another position so as to be able to use them tomorrow. The macro is
simple enough. My problem is how do I make it run with no input from the
user? But still give the user time to print a report prior to the macro
running.
--
Jimmy

Hey Jimmy D

sorry, I don't have an excel97 lying around.

But it should work anyways:
http://msdn.microsoft.com/archive/de.../D3/S5A3D4.asp

hth

Carlo

On Nov 22, 9:38 am, Jimmy D wrote:
Unsure of what you are trying to do. Have tried to follow in vb but am not
able to work out where you are. I am working with excel 97 (possibly a bit of
a dinosaur) but is all I have.
Do I maybe have to start a new vb macro to do this? or can it be done with
the existing macro?
My left drop down only contains (General) and the right has the macro name
and (Declarations)
--
Jimmy



"FSt1" wrote:
hi
you could put the code in the ThisWorkbook Before close event. or even the
before save event.
open the vb editor (alt + F11). click your VBAProject(workbook). click This
Workbook. in the left dropdown over the code window, chick This Workbook. in
the right dropdown, scroll to before close. type or paste your code.

Regards,
FSt1

"Jimmy D" wrote:

I have a set of data sums which are updated from anther excel document daily
and I need to be able to record these figures to be able to work out how much
work has been done between yesterday and today.
Since this document will be a template for other users I would like to be
able to automatically run a macro on shutdown to copy the values from today
to another position so as to be able to use them tomorrow. The macro is
simple enough. My problem is how do I make it run with no input from the
user? But still give the user time to print a report prior to the macro
running.
--
Jimmy- Hide quoted text -

- Show quoted text -

Jimmy

Might be easier to go back to the Excel window and right-click on the Excel Icon
left of "File"

Select "View code"

From left drop-down select "Workbook".

This will appear

Private Sub Workbook_Open()

End Sub

Then from right drop-down select "BeforeClose" and this will appear

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Add your macroname like so...............

Private Sub Workbook_BeforeClose(Cancel As Boolean)
macroname
End Sub

Delete the workbook_open and its end sub.


Gord Dibben MS Excel MVP



On Wed, 21 Nov 2007 17:04:45 -0800 (PST), carlo wrote:

Unsure of what you are trying to do. Have tried to follow in vb but am not
able to work out where you are. I am working with excel 97 (possibly a bit of
a dinosaur) but is all I have.
Do I maybe have to start a new vb macro to do this? or can it be done with
the existing macro?
My left drop down only contains (General) and the right has the macro name
and (Declarations)

Thanks
I was obviously trying to do as Fst1 suggested in the wrong place. Once in
the right area it worked.
--
Jimmy


"Gord Dibben" wrote:

Jimmy

Might be easier to go back to the Excel window and right-click on the Excel Icon
left of "File"

Select "View code"

From left drop-down select "Workbook".

This will appear

Private Sub Workbook_Open()

End Sub

Then from right drop-down select "BeforeClose" and this will appear

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Add your macroname like so...............

Private Sub Workbook_BeforeClose(Cancel As Boolean)
macroname
End Sub

Delete the workbook_open and its end sub.


Gord Dibben MS Excel MVP



On Wed, 21 Nov 2007 17:04:45 -0800 (PST), carlo wrote:

Unsure of what you are trying to do. Have tried to follow in vb but am not
able to work out where you are. I am working with excel 97 (possibly a bit of
a dinosaur) but is all I have.
Do I maybe have to start a new vb macro to do this? or can it be done with
the existing macro?
My left drop down only contains (General) and the right has the macro name
and (Declarations)