|   Excel's chattiness can get a little old; always prompting you to confirm actions you just asked it to perform. Quit the conversation and let Excel get back to the action . The types of prompts we are talking about are those that ask you whether you want to enable macros (when you do not have any), or whether you are sure you want to delete a worksheet. Here is how to get rid of the most common prompts once and for all. Enabling Macros When You Don't Have Any Excel's memory is like a steel trap when it comes to remembering that you recorded a macro in your workbook. Unfortunately, its memory of macros persists even though you might have since deleted one or more macros via Tools  Macro Macros (Alt/Option-F8). Reopen the workbook and you'll still be prompted to enable macros, even though there are none to enable.  | You'll be prompted to enable macros only if your security level is set to Medium. If it's set to Low, macros are enabled without a peep; if it's set to High, macros are disabled automatically for your protection. | |
When you record a macro, Excel inserts a Visual Basic module to hold your commands and home-brewed functions. Upon opening a workbook, Excel checks for the presence of modules, whether empty or macro-filled. Deleting a workbook's macros deletes any code within the module, not the module itselfkind of like drinking the last of the milk, yet putting the empty carton back in the fridge . To avoid the unnecessary macro prompt, you need to remove the module. Here's how to do that. Open the VBE by selecting Tools Macro Visual Basic Editor (or by pressing Alt/Option-F11) and select View Project Explorer. (On the Macintosh, the Projects window is always open, so you don't need to open the Project Explorer.) You'll see a window such as that shown in Figure 1-8. Figure 1-8. Project Explorer modules with Modules folder expanded  Find your workbook in the Project Explorer and click the plus sign (+) to its left to expose the workbook's component parts , particularly the modules. Click the plus sign to the left of the Modules folder to list any modules on board. Right-click each module in turn and choose Remove Module from the context sensitive menu. Decline the offer to export the modules. Before blithely removing modules that might contain useful code, double-click each module in turn to make certain you don't need them. Press Alt/  -Q as usual to get back to Excel's spreadsheet view. Prompting to Save Nonexistent Changes You might have noticed that sometimes simply opening a workbook and taking a look around is enough to trigger Excel to prompt you to save changes to your personal macro workbookdespite the fact that you've made no changes whatsoever. Whether you know it or not, you most likely have a volatile function within your personal macro workbook. A personal macro workbook is a hidden workbook created the first time you record a macro and opened each time you use Excel. A volatile function (or formula) is one that automatically recalculates each time you do almost anything in Excel, including opening and closing either the workbook or the entire application. Two of the most common volatile functions are the Today( ) and Now( ) functions. So, although you might believe you've made no changes to the workbook at hand, those volatile functions running in the background might have. This counts as a change and triggers Excel's prompt to save said invisible changes. If you want Excel to stop prompting you to save changes you didn't make, you have a couple of options open to you. The most obvious is not to store volatile functions within your personal macro workbook in the first place, and to delete any volatile functions that are already there. Or, if you need volatile functions, you can use this rather simple snippet of code to circumvent the check by tricking Excel into thinking your personal macro workbook has been saved the moment it opens: Private Sub workbook_Open( ) Me.Saved = True End Sub This code must live in the private workbook module of your personal macro workbook. To get there from any workbook, select Window Unhide, select Personal.xls from Unhide Workbook, and click OK. Visit the VBE and enter the aforementioned code. Press Alt/ -Q to get back to Excel when you're done. Of course, if you have a volatile function you want to recalculate and you want to save the changes, you need to explicitly tell Excel to do so: Private Sub workbook_Open( ) Me.Save End Sub This macro will save your personal macro workbook automatically each time it is opened. Stopping Excel's Warning Prompts for Recorded Macros One of the many drawbacks of recorded macros is that, although they're pretty good at mimicking just about any command, they tend to forget your responses to prompts. Delete a worksheet and you're prompted for confirmation; run a macro for the same and you'll still be prompted. Let's turn off those prompts. Select Tools Macro Macros (Alt/Option-F8) to bring up a list of your macros. Make sure All Open Workbooks is selected in the Macros In: box's pull-down menu. Select the macro you're interested in and click the Edit button. Put the cursor before the very first line of codethe first line without an apostrophe in front of it, that isand prepend the following: Application.DisplayAlerts = False At the very end of your code, append the following: Application.DisplayAlerts = True Your macro should look something like this: Sub MyMacro( ) ' ' MyMacro Macro ' Deletes the Active worksheet ' ' Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub Note that you've turned alerts back on at the end of your macro to reenable standard Excel prompts while working in Excel. Leave this out, and you'll see no alerts at all, not even those that might have been good to include.  | If your macro does not complete for any reasona runtime error, for instanceExcel might never get to the line of code that turns alerts back on. If this happens, it's probably wise to quit and restart Excel to set things back to the way they were. | |
Now you know how to use Excel without prompts. Be aware, though, that these prompts are there for a reason. Make sure you fully understand the purpose of a prompt before summarily turning it off. | 