VBA Application Level Events - BeforePrint

[jeremyhamblen]

I've exhausted my searches trying to bang this out, when I know I can solve my probably in an un-sexier manor by creating a macro that overrides CTRL+P to run spell check and then print. However, I am hell-bent on figuring this thing out.

My goal: to force excel to run spell check before I print, or in my current code, give me the option to

Status: Everything in my code works, except for the fact that I cannot get it to work at the application level. That means, the macro is written in personal.xls and works with personal.xls if i try print out of personal.xls. It also works in other workbooks, but here is the rub. If i close out of everything and restart excel, it does not work with other workbooks unless I unhide personal.xls, print from there (bringing up the spell check/print process), and then move back to the other workbook where it begins working for that workbook and any other...until I close and reopen excel again.

Here is how I coded it:

Class Module "Class1" of personal.xls

Private WithEvents App As Application

Private Sub Class_Initialize()
    Set App = Application
End Sub

Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)

    Dim MyAnswer As String
    Dim MyNote As String

        MyNote = "Spell Check?"
        MyAnswer = MsgBox(MyNote, vbQuestion + vbYesNo, "Spell Check")

        If MyAnswer = vbYes Then
            Cells.CheckSpelling
        Else
        End If
End Sub

Then, in "ThisWorkbook" on personal.xls

 Private XLApp As Class1

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Set XLApp = New Class1
End Sub

Again, this all works outside of getting it recognize the code each time I start excel. I may not be saying that right, but then again, there is likely plenty that I'm not doing right.

Thanks in advance

 

[combo]

You never print your Personal.xls, so the XLApp is not initialised. Do it in the Workbook_Open (in Personal).

combo