"Mark" wrote:

Hi,
Im trying to get a worksheet change event to work across multiple
templates. Ive tried going through the newsgroup, Chip Pearsons site,
Erlandsen Data site, and a few others and havent really seen anything that
addresses my question. Im trying to use Dave McRitchies code to insert a
row in a sheet via a double click:

Cancel = True 'Eliminate Edit status due to doubleclick - from Dave
McRitchie 09/08/08
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstant s).ClearContents
On Error GoTo 0
End Sub

This works fine for the sheet where the code resides in the Personal.xls,
however I would like for it to work across 3 sheets in 6 different workbooks,
all templates. Ive created a class module for the code and activate it in
€œThis Workbook€�, but it doesnt work. Ive also made sure that enable events
is set to true. How can I make this work? Thanks.
Mark


To my knowledge, and I may be wrong, you can't create a generic event
handler to work with mutliple workbooks/sheets. What you can do however is
to plop the following code into a module then call this sub in the event
procedures for each workbook/sheet:

Public Sub DoMyCode(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstant s).ClearContents
On Error GoTo 0
End Sub

As an example:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Call DoMyCode(Target, Cancel)
End Sub