Testing for an Application level dialog in Excel 2010

Question

Excel 2010 has an annoying feature where you can click a few buttons and have Excel email out the current file you're working on. As far as I can tell, this happens without triggering any sort of save (because I have code that kicks in when an On Save event fires). This is annoying because I've deliberately set up my Excel app to prevent saving of the file in anything other than PDF format and this particular piece of Microsoft provided functionality completely removes my control.

When I record a macro and access this function (via File | Save & Send | Send as Attachment), the macro recorder shows that hitting this sequence causes the following code to fire:

Application.Dialogs(xlDialogSendMail).Open

As far as I can tell, this is the only code that fires.

My question is this: Is there any way to test for this event so that I can either stop it in its tracks or at the least pop up some sort of dialog box to alert the user that this should not be done? Ideally, I need to know how to do this in both Excel 2010 and Excel 2007 because my company uses both.

Answer 1

AFAIK there is no way you can trap that in Excel VBA.

Having said that, here is an ALTERNATIVE to achieve what you want ;)

This will make the entire Save and Send option unavailable for your workbook only.

First download the Custom UI Editor from here.

http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx

After you have installed the Custom UI Editor, start it and follow the instruction given in the snapshot below.

Now when you open your file, you can see that Save And Send is Unavailable.

EXCEL 2010 CODE

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <backstage>

        <tab idMso="TabShare" visible="false"/>

    </backstage>
</customUI>

EXCEL 2007 CODE

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="false">
    <officeMenu>
      <menu enabled="false" idMso="FileSendMenu" showImage="true" showLabel="true" />
    </officeMenu>
  </ribbon>
</customUI>

HTH

FOLLOWUP

Siddarth - how do I make this work for both Excel 2007 and Excel 2010? Does this mean I need a version of my file for each version of Excel? – Jay 11 mins ago

TRIED AND TESTED

If you want your file to work for both 2007 and 2010 then insert both sections "Office 2007 Custom UI Part" and "Office 2010 Custom UI Part"

More Snapshots