Sunday, October 19, 2008

Excel Macro Confirm MSG Box

I recently realized a danger in keyboard-shortcut driven macros. Excel allows you to tie a macro to common shortcuts (like CTRL-C, CTRL-B) and if pressed accidentally, changes made by the macro can not be undone. As a result i came up with this simple bit of code to force confirmation before the macro is run, through a MSG Box.

Simply place this at the beginning of each of your macros:

ans = MsgBox("Run Macro?", vbYesNo)
If ans = vbNo Then
Exit Sub
End If

If the user says "NO" the macro sub will exit, and if yes, the code below this section will be run. An easy way to protect users from accidentally misusing your macros.

Labels: , , , , ,