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
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.
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.
