Bypass Beforesave Macro while developing

**MarkH000** · 07-14-2011, 03:17 AM

I have a beginners knowledge of VBA macros in Excel 2007. I have developed a beforesave VBA macro for excel 2007 that checks that a number of cells have non-blank, non-zero values in them to force users to complete certain cells before saving. This seems to work quite well The workbooks are used to feed into a Word Mail Merge and it saves incomplete letters being sent out.

My catch is with me saving the file. How do I save master copies of this workbook with the cells left blank or zero (the default) and hence override or bypass or ignore the beforesave macro? This is primarily while I am developing and testing the new excel workbook and so I can send out master copies to the other users.

**mohd9876** · 07-14-2011, 03:21 AM

you can disable events temporarily and then re enable them:

Application.EnableEvents = False
'
'
'
Application.EnableEvents = True

**MarkH000** · 07-14-2011, 03:33 AM

A further questions (pls excuse my ignorance):

How do I overcome the final save issue where I want to save the s/sheet in its master form with blanks and zeroes and the beforesave macro ready to operate.

**mohd9876** · 07-14-2011, 03:39 AM

like i said in the previous post, when you want to make master copies,, disable the events and after you have finished saving the copies re enable them for example:

Sub SaveCopies()
Application.EnableEvents = False
ActiveWorkbook.SaveAs "Copy1"
ActiveWorkbook.SaveAs "Copy2"
'
'
'
Application.EnableEvents = True
End Sub

**MarkH000** · 07-14-2011, 03:48 AM

I'm just a bit slow. I think I understand now. Thank you so much for your guidance.

**mohd9876** · 07-14-2011, 03:52 AM

you are welcome :D
Please if you are satisfied with the answer mark your thread as solved

all the best

**CBG05QB** · 08-01-2011, 10:59 AM

Mark:

i am fairly new to VBA myself and I am working on a macro do do something very similar, could you post your code, I am hjaving a difficult time getting mine to work.

Brad

**MarkH000** · 08-01-2011, 05:13 PM

CBG05QB, No bother.

As per the suggestion of mohd9876, I simply have a macro as below and do a run macro everytime I wish to save the file. The typical users of the Excel file won't be aware and hence will only successfully save the file when the cells are correctly entered. If you wish to save to a different file, you need to replace .Save with .SaveAs.

-------
Sub SaveCopies()
Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True
End Sub
------

Hope it helps.