MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Getting VBA to answer Name Conflict dialog box automatically with Yes


Posted by Mick on February 13, 2002 12:24 PM

I have written a macro to copy all the data cells from one file to another. I have several named ranges, which are identical on both files, but I use the named ranges in my cell Validation. When I copy the cells over, if there is a validation which uses the named range, Excel pops up with a dialog box asking if I want to use the name on the destination sheet or not. The default is Yes but I can't figure out how to write a code that would always simulate Yes whenever this dialog box comes up. Any suggestions?


Posted by Richard Winfield on February 13, 2002 1:09 PM


Add this line to the macro prior to the copy routine :
Application.DisplayAlerts = False

Then turn it back on after the file copy routine:
Application.DisplayAlerts = True

This turns off the excel alerts and excel defaults to YES on any alerts.

Rick

Posted by Mick on February 13, 2002 1:21 PM

Hooray, that works wonderfully. I knew someone out there would know how to fix my problem. Thanks so much.

-Mick