Visual Basic in Exel Macro - Answer Windows Dialog Box?

Question

I use several Excel macros to reformat some HTM files into Excel (complex parsing/formatting) and also to add data to existing workbooks by VLOOKUP & other functions.  Two of these macros always produce Windows dialog boxes - one includes a Save As that replaces an existing file plus edits a resource workbook (renames the tab), but then closes it (and I don't want to save the tab rename), and the other file does a text to columns conversion that replaces old data, and I want it to always accept the replacement.  The latter I could work around by changing the way I do the work, but the former two, will always be that way.  So I'm wondering, is there a way to add Visual Basic code that will always answer the Windows dialog box for me (e.g. Yes, I want to save over this file, No, I don't want to save the worksheet rename to this file, and Yes, I do want this text to columns conversion to replace the existing data.  If not, I can always be sitting here and answering the dialog boxes, but if I can add code, I'd rather.  Thanks in advance!  I'm hoping the answer is VB code to add to my macro.  In anticipation that it is, if I need to know a "Name" for the dialog box, please include how I find the "name" of the dialog box to include in the code.

Answer 1

Try using:

    Application.DisplayAlerts = False

    'Your code...

    Application.DisplayAlerts = True

If that doesn't work, please post your code as I'm sure there will be another way to achieve what you need without the dialogs and messages being produced in the first place....

Cheers

Rich

Answer 2

Thanks so much, Rich!

Answer 3

Yes, you've understood correctly.  If you want other dialogs to show, you should only wrap the lines of code which produce the dialogs that you want to suppress.

Cheers

Rich

PS. See the Help file: http://office.microsoft.com/client/helppreview.aspx?AssetId=HV805502629990&lcid=2057&NS=EXCEL%2EDEV&Version=12&queryid=&respos=1&HelpID=vbaxl10%2Echm65879 for the Note on Save As...

Answer 4

That worked.  I have a  question as to what the meaning of the code is, as it relates to my code.  If I understand correctly, the first code (= False) turns off the Windows dialog boxes produced by my code, and the second code (= True) turns those Windows dialog boxes back on.  Is this correct?  I want to understand (a) because I'm curious, but also (b), if any extra errors occur that produce a Windows dialog box, I do want it to stop in it's tracks, so I will then only insert this on/off toggle at the precise points where the dialog boxes are always produced.  Please let me know.  Thanks!