MultiWorkbook Change Event Detection / Spell Checker
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" wrote:
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
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown
"Dreiding" wrote:
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
|