Running a Script upon Saving a File

I was wondering if someone can help me.

I am trying to help a friend with a project he is working on. He is using
excel to store and manage simple inventory data. (He won't use Access.) He
wants to keep track of who/when someone makes changing to the file and saves
it. So, he asked me to write a script to do this for him.

Let say I make a change to the data contained in the worksheets and then
save the file. He would like a script to be run that would ask for a name and
store that information along with the date stored in the worksheet that was
changed.

Unfortunately, its been a while since I last wrote anything in Visual Basic.
I also do not know if you can do such a thing when someone saves a file.

The only thing I can think of is to have a macro on each sheet and have them
click it whenever they change something. Is there a better way?

I would appreciate any assistance or advice anyone would like to offer.

Thanks,

-Michael

> Let say I make a change to the data contained in the worksheets and then
> save the file. He would like a script to be run that would ask for a name and
> store that information along with the date stored in the worksheet that was
> changed.


It sounds like you want to use the workbook's BeforeSave Event :

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

'code here

End Sub

In the VBA Editor window, use View-Project Explorer, then select
ThisWorkbook , use the left dropbox at the top to select Workbook, and then
the right drop box to select BeforeSave

Start out by looking at Chip Pearson's page on events

http://www.cpearson.com/Excel/events.htm

Looks like you would be interested in the workbook level Change event and
the BeforeSave event.

--
Regards,
Tom Ogilvy


"Michael" <[email protected]> wrote in message
news:[email protected]...
> I was wondering if someone can help me.
>
> I am trying to help a friend with a project he is working on. He is using
> excel to store and manage simple inventory data. (He won't use Access.) He
> wants to keep track of who/when someone makes changing to the file and
saves
> it. So, he asked me to write a script to do this for him.
>
> Let say I make a change to the data contained in the worksheets and then
> save the file. He would like a script to be run that would ask for a name
and
> store that information along with the date stored in the worksheet that
was
> changed.
>
> Unfortunately, its been a while since I last wrote anything in Visual
Basic.
> I also do not know if you can do such a thing when someone saves a file.
>
> The only thing I can think of is to have a macro on each sheet and have
them
> click it whenever they change something. Is there a better way?
>
> I would appreciate any assistance or advice anyone would like to offer.
>
> Thanks,
>
> -Michael
>

Got it! Thanks guys!

"mark" wrote:

> > Let say I make a change to the data contained in the worksheets and then
> > save the file. He would like a script to be run that would ask for a name and
> > store that information along with the date stored in the worksheet that was
> > changed.
>
>
> It sounds like you want to use the workbook's BeforeSave Event :
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
>
> 'code here
>
> End Sub
>
> In the VBA Editor window, use View-Project Explorer, then select
> ThisWorkbook , use the left dropbox at the top to select Workbook, and then
> the right drop box to select BeforeSave