Solved: Is there a way to have code trigger an event when trying to save or print?

You can use the routine that disables the send button on workbook open when certain cells aren't filled in. If there is a value in all the cells that you need use the enable routine. Tested this under excel 2003.[vba]Option Explicit
'Link to numbers of id
'http://support.microsoft.com/kb/213552#top
Sub disable_menu_items()
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=30095)
'mail menu under file - send
Ctrl.Enabled = False
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=3738)
'mail button on toolbar
Ctrl.Enabled = False
Next Ctrl
End Sub
Sub enable_menu_items()
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=30095)
Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=3738)
Ctrl.Enabled = True
Next Ctrl
End Sub[/vba]

Hmm that looks like the trick :)
I think i'll even make this an always on situation, I don't want anyone mailing from within the doc.
Shall I copy this to "this workbook"?

The disable routine goes in the workbook_open event. The enable routine goes in the workbook_before close event.

Thanks!!

(i'm so loving this!)

compile error: procedure declaration does not match description of event or procedure having the same name
[VBA]Private Sub Workbook_open()
Sub disable_menu_items()
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=30095)
'mail menu under file - send
Ctrl.Enabled = False
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=3738)
'mail button on toolbar
Ctrl.Enabled = False
Next Ctrl
End Sub
End Sub
Private Sub workbook_beforeclose()
Sub enable_menu_items()
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=30095)
Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=3738)
Ctrl.Enabled = True
Next Ctrl
End Sub
End Sub[/VBA]

I added and removed the sub enable menu items and it's end sub, but that made no diff.
missing something?

Put those routines in a normal module and use a call.[VBA]Call disable_menu_items[/VBA]for workbook_open.

This is what module3 looks like now:

[vba] Private Sub Workbook_open()
Call disable_menu_items
End Sub
Private Sub Workbook_beforeclose()
Call enable_menu_items
End Sub


Sub disable_menu_items()
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=30095)
'mail menu under file - send
Ctrl.Enabled = False
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=3738)
'mail button on toolbar
Ctrl.Enabled = False
Next Ctrl
End Sub

Sub enable_menu_items()
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=30095)
Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=3738)
Ctrl.Enabled = True
Next Ctrl
End Sub
[/vba]

it doesn't seem to interfere with the emailing commands at all.

edit: I saved closed and reopened the doc, but nothing changed.

???

I tested the call with a worksheet change event
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("c5"), Target) Is Nothing Then
Call disable_menu_items
End If
End Sub[/VBA]
and it works but apparantly i can't have two worksheet_change events on the same sheet, it gives an ambiguous name error. (so I can't use that as a workaround)

back to the on open and before close events...

?? don't get it. I reverted to a previos save and now it works.

regardless, thanks charlize!!

[yuck, now it has permanently disabled the controls, back to the backend....]

no that was the residual disable from the test i did. the global oen even still doesn't take effect

Use my file. Open it and see that the send button is disabled. Close that file and just start a new file. You'll see that the send button is enabled. Meaning that for my file the send button is disabled when you open it and enabled again when you close the workbook.

I copied everything and now it works. Still don't know what I did wrong....
is the option specific an integral part?

Just a couple of housekeeping things:
1. You can only have one of each event for the object it belongs to - so only one worksheet_change event per worksheet. You can however, have multiple routines called from that event.
2. If you are going to disable controls in the workbook_open event and re-enable in the workbook_beforeclose, you really ought to disable/enable them in the workbook's activate and deactivate events so that if the user switches to a different workbook, the controls are available.
FWIW
Rory

1. yeah I figured that out the hard way, but found code somewhere else to show me how to set it up. :)

2 You're right, it would be the polite thing to do.
can I add "copies"of the current routines, with the activate and deactivate events in their first lines?