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

Short version:
I want to generate an error message when certain fields are not filled in when the user tries to save or print the document.

Slightly longer version:
Still working on the same workbook with the pdf code (http://vbaexpress.com/forum/showthre...492#post103492 )

What I want to do, is make sure that certain fields are always filled (correctly) in before the user can print/save the document.

I want it to check only a few cells, three dates and a version number (1.2 etc)

If the cells are not filled in (correctly) by leaving them empty, or entering text, or only 1 number instead of 2 (for the version number), and they hit save/print (or try to pdf) the a popup error should appear saying that the dates or the version number has not been filled in correctly.

Is this a possibility?

You can use the Workbook_BeforeSave and Workbook_BeforePrint events for this. Code need to go into the ThisWorkbook module of your workbook.
HTH
Rory

Thanks rory!

will this conflict with an automated pdf print script or will it interrupt it?

Quote:

---

Originally Posted by rory

You can use the Workbook_BeforeSave and Workbook_BeforePrint events for this. Code need to go into the ThisWorkbook module of your workbook.
HTH
Rory

---

just a quick hijack here..
these functions are only available in office 2007 ?

cheers

Looking at that script, the BeforePrint will be triggered when you hit the PrintOut lines in the code. But if you are running that code to print to PDF, you can do the checks at the beginning of that code anyway. That means that you either don't put the check into the BeforePrint event, or you can use Application.EnableEvents = False in the PDF code, do the printing, then set it back to True.
Regards,
Rory

Daniel_d_n_r,
No, those events have been around as long as I can recall. Since Excel 97 anyway.
Regards,
Rory

I got the before save to work:
[vba]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheets("signatures and pricing").[g2] = "" Then
Cancel = True
MsgBox "Can't save. No entry for Requested Date"
Else
Cancel = False
End If
End Sub[/vba]

The same didn't work for printing ( it gave "Compile error: Procedure declaration does not match description of event or procedure having the same name.")
(this was tried for normal printing, not pdf. I changed the beforesave to before print in the above code)

I assume I have to set up two sets of code, one for the pdf script and one for regular printing.

I have no clue though what you mean me to add to the pdf code to check actually.



I found something elsewhere:

[vba]Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Sheets("signatures and pricing").[g2] = "" Then
MsgBox "Cannot print until required cells have been completed!"
Cancel = True
End If
End Sub [/vba]

This seems to work on both normal printing and the pdf code but.........

I have o clue how to add more than one cell to check ( for instance cell c2 from the sheet "information)

The BeforePrint event must look like this:
[VBA]Private Sub Workbook_BeforePrint(Cancel As Boolean)
End Sub
[/VBA]
In the VBEditor, if you choose Workbook from the left dropdown at the top of the main code window, the right dropdown contains a list of events; picking one will create a stub procedure with the right format.
All you would do with your print to PDF routine is add the check near the beginning like this:
[VBA]If Sheets("signatures and pricing").[g2] = "" Then
MsgBox "You need to fill in cell G2!"
Exit Sub
End If[/VBA]

HTH
Rory

I got a little further: I edited my last post while you wrote yours.
what do I do to make it check multiple required cells?

The simplest way is to use multiple If statements. If you are going to do this from different events and from the PDF routine, it would be better to put the checking code into a separate function which you can call from each of the routines; then if you have to edit the code, you will only need to do so in one location:
[VBA]Function IsComplete() As Boolean
IsComplete = False
If Sheets("signatures and pricing").[g2] = "" Then Exit Function
If Sheets("signatures and pricing").[g3] = "" Then Exit Function
If Sheets("signatures and pricing").[g4] = "" Then Exit Function
If Sheets("signatures and pricing").[g5] = "" Then Exit Function
If Sheets("signatures and pricing").[g6] = "" Then Exit Function
' If we got here, then all cells are filled, so OK
IsComplete = True
End Function
[/VBA]

then in your other routines you can use:
[VBA]If IsComplete = False then
MsgBox "Values are missing!"
Exit Sub
End If[/VBA]

You could also change the function to return the addresses of the incomplete cells and check If Len(IsComplete) > 0

HTH
Rory

Getting closer and closer, I can feel it :)

ok for the "before save" function i wrote:
[VBA]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If IsComplete = False Then
Cancel = True
MsgBox "Cannot save, the CRM number and the appropriates dates are not filled in"
Else
Cancel = False
End If
End Sub[/VBA]

separate function looks like this.

It cancels the save regardless of having data in those specific fields now, what did I do wrong? :)

What does the IsComplete function look like?

HAH!!!! I fell into my own trap! there was one field not filled in ( ok granted, stupid)

recap, the script works like a charm!!!!!

thanks rory!

Glad to help! :)
You might want to rewrite the IsComplete function to return the addresses of the cells that haven't been populated so you can use it in the messagebox, but that's cosmetic.
Rory

hmm, that's a neat trick. I assume I need to add to the messagebox line?

Change the function to something like this:
[VBA]Function IncompleteCells() As String
Dim strCells As String
Dim astrCells(), varItem
astrCells = Array("G2", "G3", "G4", "G5", "G6")
For Each varItem In astrCells
If Sheets("signatures and pricing").Range(varItem) = "" Then strCells = strCells & "," & varItem
Next varItem
If Len(strCells) > 0 Then IncompleteCells = Mid$(strCells, 2)
End Function
[/VBA]

then use:
[VBA]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strCells as String
strCells = IncompleteCells
If len(strCells) > 0 Then
Cancel = True
MsgBox "Cannot save, these cells are not filled in: " & strCells
Else
Cancel = False
End If
End Sub

[/VBA]

Regards,
Rory

NICE!!!

Thanks rory! That completes my project (for now, untill I decide to upgrade it).

really cool!

is the same possible to trap an email event?

I'm not sure why you want to do everything with a before_event. If some cells aren't properly filled in, don't execute the code. No need for those events.

At least for the printing, it disables the print prview code, and also the print command in the menu. the same goes for saving.

The last thing to make sure the doc isn't circulated without those fields filled in is disabling the "send as" menu command.

( the users of this doc need to be rather strictly guided to doing it the right way ;-) )

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?