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:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
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

"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

Hi Mark!

Insert a class module in VBAproject of your Personal.xls with name "clsExcel".

Code for Class Module (clsExcel):

'------------------8<----------------------------
Option Explicit

Public WithEvents XL As Application

Private Sub Class_Terminate()
Set XL = Nothing
End Sub

Private Sub XL_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)
If TypeName(Sh) = "Worksheet" Then
Cancel = True 'Eliminate Edit status due to doubleclick - from Dave
'McRitchie 9 / 8 / 8
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
Debug.Print Sh.Name
End If
End Sub
'------------------8<----------------------------

Code for ThisWorkbook code module of PERSONAL.xls:

'------------------8<----------------------------
Option Explicit
Dim objXL As clsExcel

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved Then Set objXL = Nothing
End Sub

Private Sub Workbook_Open()
Set objXL = New clsExcel
Set objXL.XL = Excel.Application
End Sub
'------------------8<----------------------------

Ready!

--
John


Ο χÏ�ήστης "Magius96" Î*γγÏ�αψε:

"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

Thanks, Magius96. I was afraid that I would have to do something like that,
but I was looking for the magic bullit.
Mark

"Magius96" wrote:

"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

Thanks, John, but I can't seem to get it to work.
Mark

"John_John" wrote:

Hi Mark!

Insert a class module in VBAproject of your Personal.xls with name "clsExcel".

Code for Class Module (clsExcel):

'------------------8<----------------------------
Option Explicit

Public WithEvents XL As Application

Private Sub Class_Terminate()
Set XL = Nothing
End Sub

Private Sub XL_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)
If TypeName(Sh) = "Worksheet" Then
Cancel = True 'Eliminate Edit status due to doubleclick - from Dave
'McRitchie 9 / 8 / 8
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
Debug.Print Sh.Name
End If
End Sub
'------------------8<----------------------------

Code for ThisWorkbook code module of PERSONAL.xls:

'------------------8<----------------------------
Option Explicit
Dim objXL As clsExcel

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved Then Set objXL = Nothing
End Sub

Private Sub Workbook_Open()
Set objXL = New clsExcel
Set objXL.XL = Excel.Application
End Sub
'------------------8<----------------------------

Ready!

--
John


Ο χÏ�ήστης "Magius96" Î*γγÏ�αψε:

"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

Sorry, John - after I closed Excel and reopened it, it works like a charm!
Thanks for your help, I really appreciate it.
Mark

"John_John" wrote:

Hi Mark!

Insert a class module in VBAproject of your Personal.xls with name "clsExcel".

Code for Class Module (clsExcel):

'------------------8<----------------------------
Option Explicit

Public WithEvents XL As Application

Private Sub Class_Terminate()
Set XL = Nothing
End Sub

Private Sub XL_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As
Range, Cancel As Boolean)
If TypeName(Sh) = "Worksheet" Then
Cancel = True 'Eliminate Edit status due to doubleclick - from Dave
'McRitchie 9 / 8 / 8
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
Debug.Print Sh.Name
End If
End Sub
'------------------8<----------------------------

Code for ThisWorkbook code module of PERSONAL.xls:

'------------------8<----------------------------
Option Explicit
Dim objXL As clsExcel

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved Then Set objXL = Nothing
End Sub

Private Sub Workbook_Open()
Set objXL = New clsExcel
Set objXL.XL = Excel.Application
End Sub
'------------------8<----------------------------

Ready!

--
John


Ο χÏ�ήστης "Magius96" Î*γγÏ�αψε:

"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

Restart Excel Mark.
It needs to make the objXL alive.

Try again...

Ο χÏ�ήστης "Mark" Î*γγÏ�αψε:

Thanks, John, but I can't seem to get it to work.
Mark