Worksheet Change Event - Save file after tenth change

Hi all

Is there a way in vba to delay a worksheet change event by a number and only fire "ActiveWorkbook.Save" after the tenth change, rather than every change.

Add a Static variable to the Change procedure. Increment it every change and Save when it reaches 10 or a multiple of.

Basic information only - you need to understand what these are and how they work - have a read of the link.

Thanks Cytop but that lost me a bit - went with this while I work out haw to reference the static variable

Please Login or Register  to view this content.

Confused... why put the code in the Selection_Change event when you mentioned the Change event earlier? As coded the workbook will be saved after every 10th selection of any cell.

Never mind - to save after every 10 changes

Please Login or Register  to view this content.

The difference between

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

is that NumChanges will be destroyed when the procedure exists when it is simply Dim'ed so it will never get to 10. Declaring it as Static preserves the value after the procedure exists.

Essentially you have done the same thing by storing the value in a worksheet, but why add the complication of interacting with a worksheet when you can simply use a static variable that's tucked away somewhere safe and can't be changed accidentally by other procedures or by the user simply clearing cell F10...

@Cytop
I had just noticed that the sheet code defaulted to Selection_Change so your response was timely and much appreciated. Hopefully this will assist a user who has no concept of saving his changes until days end lol