Prevent code running

S

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
 
S

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
 
R

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
 
H

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top