Trapping and preventing a form from being e-mailed before validation

[quartz]

Hello, I'm using VBA in Excel XP with Win 2000

I have developed a form with a variety of on-sheet controls. I also have an on-open event performing a variety of functions

Is there a way to prevent a user from e-mailing an open Excel file to someone else? Ideally it would function like the "Before_Save" event and would trap any method of attempting to e-mail the form (such as by shortcut key, menu, etc.).

The form really needs to be validated before e-mailing and that should be performed by clicking one of the control buttons on the sheet and not by short cut key nor by using the menu as such methods won't invoke my validation routine (which will only work if the entire form is completed)

If there is no sure fire trapping method, does anyone have a list of the methods my code should counter to prevent this from occurring? If this is the case, your example code on how to trap other methods would be very helpful. Thanks in advance for your assistance.

 

[Patrick Molloy]

There's not much that you can do...your user can save
your workbook & re-open it with macros OFF which stop any
code from working. Alternatively they could copy to
another workbook & then there'd be no events.
My best solution would be to disable the "send mail"
button entirely, or re-point th ebutton to your checking
routine.

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hello, I'm using VBA in Excel XP with Win 2000.

I have developed a form with a variety of on-sheet

controls. I also have an on-open event performing a
variety of functions.

Is there a way to prevent a user from e-mailing an open

Excel file to someone else? Ideally it would function
like the "Before_Save" event and would trap any method of
attempting to e-mail the form (such as by shortcut key,
menu, etc.).

The form really needs to be validated before e-mailing

and that should be performed by clicking one of the
control buttons on the sheet and not by short cut key nor
by using the menu as such methods won't invoke my
validation routine (which will only work if the entire
form is completed).

If there is no sure fire trapping method, does anyone

have a list of the methods my code should counter to
prevent this from occurring? If this is the case, your
example code on how to trap other methods would be very
helpful. Thanks in advance for your assistance.