Prevent code running

[Sandy]

Is there any way I can prevent the 'Workbook_BeforeClose' code module
running at the point where I have inserted the asterisks

Sub NewName()

Application.ScreenUpdating = False
Application.EnableEvents = False

'other code

ThisWorkbook.SaveCopyAs filename:="C:\Users\Sandy\Documents\NewBook.xls"

ThisWorkbook.Close SaveChanges:=False

************

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Thanks
Sandy

 

[Sandy]

Sorry got the pasting wrong

If I do this

Sub NewName()

Application.ScreenUpdating = False
Application.EnableEvents = False

'other code

ThisWorkbook.SaveCopyAs filename:="C:\Users\Sandy\Documents\NewBook.xls"

ThisWorkbook.Close SaveChanges:=False

************

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

then the Before_Close does not run but the EnableEvents and ScreenUpdating
remain false next time the application is opened.

And if I do this

Sub NewName()

Application.ScreenUpdating = False
Application.EnableEvents = False

'other code

ThisWorkbook.SaveCopyAs filename:="C:\Users\Sandy\Documents\NewBook.xls"

Application.ScreenUpdating = True
Application.EnableEvents = True

ThisWorkbook.Close SaveChanges:=False

************


End Sub

then the Before_Close runs.

Hope this makes sense
Sandy

 

[Rick Rothstein \(MVP - VB\)]

That is because you are saving your file with the updating and events off...
just turn them back on **before** you do the save.

Sub NewName()

Application.ScreenUpdating = False
Application.EnableEvents = False

'other code

Application.ScreenUpdating = True
Application.EnableEvents = True

ThisWorkbook.SaveCopyAs Filename:="C:\Users\Sandy\Documents\NewBook.xls"

ThisWorkbook.Close SaveChanges:=False

End Sub

Rick

 

[Howard Kaikow]

Anytime you need to prevent code, be it in an event or not, from running in
particularcirumstances, you can do this by:

1, Create a Boolean Public variable, say bWatchSandyRun
2. Set the value to vbTRue or vbFalse,as needed.
3. With the code block, be itt event code or not, test the bWatchSandyRun to
detertmine when code should run.