Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MultiWorkbook Change Event Detection / Spell Checker
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MultiWorkbook Change Event Detection / Spell Checker
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MultiWorkbook Change Event Detection / Spell Checker
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MultiWorkbook Change Event Detection / Spell Checker
Gary,
It works great! Thanks. - Pat "Gary Brown" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spell Checker | Excel Worksheet Functions | |||
Spell Checker | Excel Programming | |||
Spell Checker | Excel Programming | |||
Spell checker | Excel Discussion (Misc queries) | |||
Spell checker | Excel Programming |