Excel VBA 25 - Suppress Alerts

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jul 15, 2010.
Some actions in the Excel interface cause an alert to appear, such as the Are you sure you want to delete this worksheet?" message. These alerts can be annoying in a macro. This episode takes a look at how to remove that alert
maxresdefault.jpg


Transcript of the video:
MrExcel podcast is sponsored by Excel-XL.
Excel VBA Chapter 25; Suppress Alerts.
Hey welcome back to MrExcel netcast. I'm Bill Jelen.
We're working through the VBA Macros book for chapter 25.
I wanna talk about this one particular situation, there's many times, when we do something in Excel and Excel warns us, hey are you sure you want to do that?
Now, one classic example is when we delete a worksheet, brings up this message says, Hey there's data there, you're gonna permanently delete this data?
and you click Delete to confirm that.
Well, that's good when you're working in the Excel interface but if you are running a macro, you know the macro does, 40 steps, pulls some data, creates some sheets, creates some pivot tables, creates some charts and now needs to clean up and delete those sheets as well.
I'm not sure I want to ask the person running the macro, if they want to confirm the deletion.
In fact, I'm sure I don't and besides, the whole point of a macro is to make life easier, you start the macro running, you go get coffee, you come back then before it's done.
You'd need to come back and have that message sitting there.
So when we delete a sheet in VBA, if we run this macro, it stops and it says, hey are you sure you want to delete?
yes or no? All right so to prevent that from even popping up, we can use application dot Display Alerts equals False, so that prevents any alerts from showing up now.
After that line of code, where you're expecting Alert, you should probably go back and change it back to True.
That way in case we have some other unexpected alert later, the alert will pop up. All right so now, I run sheet 2 and there's run sheet 2 and I'll run that code, you can watch it down here.
Click Run, Bam! It's just deleted, no warn, no hassles, everything finishes.
So, a cool trick especially if, you're making macros, you know that do a lot of steps, you don't want to be asked that question every day.
We have being stopping from asking that question.
Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top