VBA Application Event - 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"

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"

 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]

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.

 

[jeremyhamblen]

Please?

 

[Norie]

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]

Modify the checkspelling statement to

            Wb.ActiveSheet.Cells.CheckSpelling

Modify the ThisWorkbook module to

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"

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"

 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]

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.