run a macro when workbook is changed and closed

[orsm6]

Hi all - i hope this makes sense.

I wanted to try and capture somehow when someone has made a change to an excel sheet by running a macro only when the content is changed and then the user closes the workbook and clicks to save the changes. ... so somehow on a workbook close event.

if no changes made, the workbook simply closes.

the macro is to timestamp a cell on a hidden sheet each time the workbook is edited and saved.
- one way will be when they make a change, then click an icon i have made to save the change.
- but if they make a change and they didn't click the icon (to try and avoid the time stamp) by clicking the red X then the macro runs.

again, hope that makes sense.
TIA

 

[CephasOz]

If you place code in the Workbook_SheetChange event, it will run whenever a sheet is changed, so your code can record the user/date/time and cell changed. If the user then saves the workbook, your log record will also be saved. If they don't save it, it doesn't matter because the change is also not saved. So you only need the Workbook_SheetChange event.

Don't forget to set Application.EnableEvents = False before you write your log entry, and turn it back on afterwards (otherwise Excel will lock up because your log routine will fire Workbook_SheetChange which will cause a log entry which will fire Workbook_SheetChange which will case a log entry which ....). Or you could check sh.Name and only run the log code if it doesn't refer to your log sheet.

See: Workbook.SheetChange event (Excel)

 

[orsm6]

If you place code in the Workbook_SheetChange event, it will run whenever a sheet is changed, so your code can record the user/date/time and cell changed. If the user then saves the workbook, your log record will also be saved. If they don't save it, it doesn't matter because the change is also not saved. So you only need the Workbook_SheetChange event.

Don't forget to set Application.EnableEvents = False before you write your log entry, and turn it back on afterwards (otherwise Excel will lock up because your log routine will fire Workbook_SheetChange which will cause a log entry which will fire Workbook_SheetChange which will case a log entry which ....). Or you could check sh.Name and only run the log code if it doesn't refer to your log sheet.

See: Workbook.SheetChange event (Excel)

thanks for this info.....

the sheet that i want this macro in is created by another macro \... so the user fills in a template, clicks upload, saves the active sheet to a new workbook. so is it possible that i can store the macro in the link you provided on the sheet itself rather than in a module? otherwise it would not be saved across on the initial upload. hope that makes sense too lol

 

[orsm6]

now that i think about it, not sure this would work.

the macro i need is sitting in the template, when a change is made it'll try and do the timestamp... but i actually don't want the macro to run until the user has finished the template and uploaded it... where at that point it is just 1 sheet in the workbook. when it gets to that stage i want the macro to start time stamping any changes.

thanks for your help, will have to think about this one.

 

[CephasOz]

You can create a macro-enabled template (.xltm), so any macros can reside there. Perhaps, when the user has filled in the current template, you can create a new workbook from the new macro-enabled template, copy the data across*, and save and upload this new file? (*You'll need to turn EnableEvents off before copying the data so that that activity doesn't get logged.)

 

[orsm6]

You can create a macro-enabled template (.xltm), so any macros can reside there. Perhaps, when the user has filled in the current template, you can create a new workbook from the new macro-enabled template, copy the data across*, and save and upload this new file? (*You'll need to turn EnableEvents off before copying the data so that that activity doesn't get logged.)

yeah i think i understand that. thank you for your help, i'll have a look into this.