run a macro when workbook is changed and closed

orsm6

Active Member
Joined
Oct 3, 2012
Messages
379
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

CephasOz

Board Regular
Joined
Feb 18, 2020
Messages
206
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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)
 
Last edited:

orsm6

Active Member
Joined
Oct 3, 2012
Messages
379
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Oct 3, 2012
Messages
379
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Feb 18, 2020
Messages
206
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Oct 3, 2012
Messages
379
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,796
Messages
5,626,929
Members
416,209
Latest member
tan21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top