MultiWorkbook Change Event Detection / Spell Checker

[Dreiding]

Excel 2003 or 2007. Looking to be able to force spell checking whenever any
cell content is is changed. I've been successful on a single workbooks by
adding Spell Check execution on Worksheet_Change.

I'm hoping to take this one step further - make it an 'Add-In' so the check
will run for any workbook. It looks like the Worksheet_Change event only
triggers for the host workbook so an 'Add-in' will not do the job.

Any suggestions to force spell checking on a change for any
workbook/worksheet I modifiy?

TIA,
- Pat

 

[Gary Brown]

The way I would go about this is to create a CLASS in my add-in or
Personal.xls that fires up any time there is a Sheet Change in any workbook.
Steps to do this...

1) Create a Class Module in the VBE
- INSERT > CLASS MODULE
2) Rename the Class Module 'Class_SheetChange'
3) In the Class Module 'Class_SheetChange',
create an application class called 'App_WkshtChange'
by putting the following line at the top of the module....

Public WithEvents App_WkShtChange as Application

4) Next comes the procedure that tells Excel
to look in each worksheet change...

Private Sub App_WkShtChange_SheetChange(ByVal _
Sh As Object, ByVal Target As Range)
'>>>>> PUT YOUR CODE HERE <<<<<
End Sub

5) In the top of the 'ThisWorkbook' module of
the add-in or Personal.xls, create a variable
for the Class Module called 'clsWkshtChange'.

Dim clsWkshtChange as New Class_SheetChange

6) To activate the class, put a SET command in the
Workbook_Open procuedure of the 'ThisWorkbook'

Private Sub Workbook_Open()
Set clsWkshtChange.App_WkshtChange
End Sub

 

[Dreiding]

Hi Gary,

Thanks for these instructions. Really helpful!
Using Excel 2003, I'm having a compile issue (Compiler Error: Expect: =)
for the
"Set clsWkshtChange.App_WkshtChange" code

My 'Class_SheetChange' class contains:

Option Explicit
Public WithEvents App_wkShtChange As Application
Private Sub App_wkShtChange_SheetChange(ByVal sh As Object, ByVal target
As Range)
MsgBox "App_wkShtChange_SheetChange" ' for testing purposes
End Sub

My ThisWorkbook code is:

Dim clsWkshtChange As new Class_SheetChange

Private Sub Workbook_Open()
Set clsWkshtChange.App_wkShtChange 'compile error @ this line
End Sub

Any suggestions?

I also tried the following in the Thisworkbook. Code compiled but had a
runtine error #13, type mismatch when selecting it as an add-in.

Dim clsWkshtChange As Class_SheetChange

Private Sub Workbook_Open()
Set clsWkshtChange.App_wkShtChange = new Class_SheetChange
End Sub



Thanks
- Pat

 

[Gary Brown]

Set clsWkshtChange.App_wkShtChange 'compile error @ this line
s/b
Set clsWkshtChange.App_wkShtChange = Application

Sorry about that.
:O<

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

 

[Dreiding]

Gary,

It works great! Thanks.

- Pat

s/b
Set clsWkshtChange.App_wkShtChange = Application

Sorry about that.
:O<

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown