Make Worksheet Event available application-wide

[Bradley C. Hammerstrom]

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.

 

[Bob Phillips]

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]

Thanks, Bob

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

Brad H.