Answering pop-up question

[Guest]

Is there a way to automatically answer questions that pop up as you are
manipulating files? For example, after performing specific functions to a
file, I have it close, but then the questoin comes up "Would you like to save
the changes you have made to file ABC?" - and the answer will always be
yes...so is there a way to code for that? Thanks in advance!

 

[darrenmcconachie]

Have you used VBA macros before?

In the ThisWorkbook module use the beforeClose event and force the
save. If you need help implementing this let me know. If there are no
macros already in the workbook this will create an extra question on
opening the book involving Macro security. There are 2 ways round this,
but let's deal with one question at a time!

Darren

 

[Andrew Taylor]

For saving and closing a workbook the best way is to use the
SaveChanges parameter of the Workbook.Close method:

ActiveWorkbook.Close SaveChanges:=True

You can also prevent certain prompts by using
Application.DisplayAlerts, e.g.

Application.DisplayAlerts = False
ActiveWorkbook.Sheets(1).Delete ' prevents "are you sure?" prompt
ActiveWorkbook.SaveAs "c:\junk.xls" ' prevents prompt if file
exists
Application.DisplayAlerts = True ' reset prompts

hth
Andrew

 

[Guest]

Matt,

You can use the Application.DisplayAlerts = False property. You can set it
to True after you have completed a dialog box action.

 

[Guest]

Hello Darren - thanks for the reply - I am using this in a VBA macro - but
I'm not familiar with the beforeClose event and forcing the save....so any
guidance on the coding would be greatly appreciated!

Thanks!

 

[Guest]

Thank you both galimi and Andrew....very helpful!

 

[Guest]

What if you want to not have the message:

"There is a large amount of information on the Clipboard. Would you like
it...blah blah"

come up?

Thanks again

 

[Andrew Taylor]

Try using
Application.CutCopyMode = False
before closing the workbook