Make Worksheet Event available application-wide
 

E2K

This works for my test workbook, but I want it to work for any workbook I
open, new or existing. In other words the code needs to be attached to
Excel, not attached to a particular workbook or worksheet. But How?

Scenario:
A custom toolbar in Personal.xls (hidden) has two buttons assigned to two
macros. The macros are stored in a Module in Personal.xls. The idea is that
when a user adds data to an existing workbook, all new or edited cells are
in a red font. (I realize Track Changes does similar, but looses highlights
after re-opening the file.)

The two macros:
-----------------------------------------------
Sub TypeInRedOn()
MsgBox "Type all new or edited cell values in Red?" & Chr(13) _
"Note: This will continue until you turn it off.", vbYesNo, "Start
TypeInRed?"
Application.EnableEvents = True
End Sub
-----------------------------------------------
-----------------------------------------------
Sub TypeInRedOff()
MsgBox ("Stop changing the font color?"), vbYesNo, "Stop TypeInRed?"
Application.EnableEvents = False
End Sub
------------------------------------------------

The EnableEvents method refers to a procedure stored in Sheet1:
------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 3
End Sub

How can I make this WorksheetEvent application-wide, rather than specific to
a single sheet?

Brad H.

Make Worksheet Event available application-wide
 

Bradley,

This is a bit more complex, but can be achieved with application events

Firstly, all of this code goes in a designated workbook, one that you will
open when you start Excel.

'========================================
Insert a class module, rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'your code or a call to your macro

End Sub

'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bradley C. Hammerstrom" wrote in message
...
E2K

This works for my test workbook, but I want it to work for any workbook I
open, new or existing. In other words the code needs to be attached to
Excel, not attached to a particular workbook or worksheet. But How?

Scenario:
A custom toolbar in Personal.xls (hidden) has two buttons assigned to two
macros. The macros are stored in a Module in Personal.xls. The idea is
that
when a user adds data to an existing workbook, all new or edited cells are
in a red font. (I realize Track Changes does similar, but looses
highlights
after re-opening the file.)

The two macros:
-----------------------------------------------
Sub TypeInRedOn()
MsgBox "Type all new or edited cell values in Red?" & Chr(13) _
"Note: This will continue until you turn it off.", vbYesNo, "Start
TypeInRed?"
Application.EnableEvents = True
End Sub
-----------------------------------------------
-----------------------------------------------
Sub TypeInRedOff()
MsgBox ("Stop changing the font color?"), vbYesNo, "Stop TypeInRed?"
Application.EnableEvents = False
End Sub
------------------------------------------------

The EnableEvents method refers to a procedure stored in Sheet1:
------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 3
End Sub

How can I make this WorksheetEvent application-wide, rather than specific
to
a single sheet?

Brad H.

Make Worksheet Event available application-wide
 

Thanks, Bob

Good info also he
http://www.cpearson.com/excel/AppEvent.htm

Brad H.