VBA Application Event - BeforePrint

jeremyhamblen

New Member
Joined
Aug 6, 2009
Messages
12
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"

Code:
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"

Code:
 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,

Jeremy
 

jeremyhamblen

New Member
Joined
Aug 6, 2009
Messages
12
If this is an easy question, or I'm violating a rule by posting it, let me know. I really have tried searching to see if this has already been addressed on the board. Any help would be appreciated.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Jeremy

I'm no expert on application level events, in fact I've never used them and I also don't have a Personal.xls workbook.

So what I'm about to say might be totally irrelavant.

What is Cells exactly?

Without a workbook/worksheet reference then Excel/VBA will assume you mean Cells on what itconsiders the active object.

Is that what you want/need?

It might even think you are referring to Cells on a worksheet in Personal.xls.:)
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,007
Modify the checkspelling statement to
Code:
            Wb.ActiveSheet.Cells.CheckSpelling
Modify the ThisWorkbook module to
Code:
Option Explicit
Dim x As Class1
Private Sub Workbook_Open()
    Set x = New Class1
    End Sub
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"

Code:
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"

Code:
 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,

Jeremy
 

jeremyhamblen

New Member
Joined
Aug 6, 2009
Messages
12
Genius. Looks like it is working just great!

Thanks a bunch for your help. Some days it feels like two steps forward and one step back with VBA.
 

Forum statistics

Threads
1,082,447
Messages
5,365,581
Members
400,840
Latest member
Fortune

Some videos you may like

This Week's Hot Topics

Top