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

[andrewvanmar]

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?

[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

[andrewvanmar]

Thanks rory!

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

[daniel_d_n_r]

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

[rory]

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

[rory]

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

[andrewvanmar]

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)

[rory]

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

[andrewvanmar]

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?

[rory]

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

[andrewvanmar]

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?

[rory]

What does the IsComplete function look like?

[andrewvanmar]

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!

[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

[andrewvanmar]

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

[rory]

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

[andrewvanmar]

NICE!!!

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

really cool!

[andrewvanmar]

is the same possible to trap an email event?

[Charlize]

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.

[andrewvanmar]

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 ;-) )