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 when employing a Macro. Today, Episode #1355 takes a look at how to remove that alert.
Transcript of the video:
MrExcel podcast is sponsered by Easy-XL.
Excel VBA Chapter 25- Suppress Alerts Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
We're working through the VBA Macros book.
For chapter 25 I want to 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.
It brings up this message says hey, there's data there You're going to permanently delete this data, and you click to delete to confirm that.
Well that's good when you're working in the excel interface but if you are running a Macro Macro does 40 steps It pulls in some data, create some sheets, crates some pivot tables, create some charts and now needs to clean up and delete those sheets well I'm not sure. I want to ask the person running the macro if they want to confirm the deletion.
Infact 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 the reports done.
You'd hate to come back and have that message sitting there, so when we delete a sheet in VBA.
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.DisplayAlerts = False So that prevents any alerts from showing up now after that the 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. Here we run sheet 2 to run that code.
You can watch down here Click Run. Bam It just deleted. No warning. 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.
You can stop it from asking that question.
Hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Excel VBA Chapter 25- Suppress Alerts Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
We're working through the VBA Macros book.
For chapter 25 I want to 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.
It brings up this message says hey, there's data there You're going to permanently delete this data, and you click to delete to confirm that.
Well that's good when you're working in the excel interface but if you are running a Macro Macro does 40 steps It pulls in some data, create some sheets, crates some pivot tables, create some charts and now needs to clean up and delete those sheets well I'm not sure. I want to ask the person running the macro if they want to confirm the deletion.
Infact 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 the reports done.
You'd hate to come back and have that message sitting there, so when we delete a sheet in VBA.
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.DisplayAlerts = False So that prevents any alerts from showing up now after that the 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. Here we run sheet 2 to run that code.
You can watch down here Click Run. Bam It just deleted. No warning. 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.
You can stop it from asking that question.
Hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
