Event procedure or ............

[Mark]

Hi,
I’m trying to get a worksheet change event to work across multiple
templates. I’ve tried going through the newsgroup, Chip Pearson’s site,
Erlandsen Data site, and a few others and haven’t really seen anything that
addresses my question. I’m trying to use Dave McRitchie’s 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(xlConstants).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. I’ve created a class module for the code and activate it in
“This Workbookâ€, but it doesn’t work. I’ve also made sure that enable events
is set to true. How can I make this work? Thanks.
Mark

 

[Magius96]

Hi,
I’m trying to get a worksheet change event to work across multiple
templates. I’ve tried going through the newsgroup, Chip Pearson’s site,
Erlandsen Data site, and a few others and haven’t really seen anything that
addresses my question. I’m trying to use Dave McRitchie’s 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(xlConstants).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. I’ve created a class module for the code and activate it in
“This Workbookâ€, but it doesn’t work. I’ve 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(xlConstants).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

 

[John_John]

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(xlConstants).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!

 

[Mark]

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

 

[Mark]

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

 

[Mark]

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]

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

Try again...

Ο χÏήστης "Mark" έγγÏαψε: