Can an add-in macro update a worksheets Worksheet_Change function?

[strataguru]

Hi,

I am wondering if there's a way I can have my add-in macro (that
creates specific worksheets as a setup for my users) update the new
worksheets Worksheet_Change function.

Basically I'd like my 'setup' macro to update some of the new
worksheets Worksheet_Change function so that that function has code
embedded in it.

Is this possible?
Thanks,
Robin

 

[keepitcool]

Robin,


If the addin is always loaded when the users are working with
those files I suggest a different approach, to keep the files clean of
macros and keeps your coding concentrated in the addin itself.

You'll work with the application object's events thus ALL open
workbooks/worksheets events can be controlled centrally.

Once you've copied it you'll have to run the workbook open to initialite
the XLapp variable. Once initiated it can start monitoring.

In the code window..
select XLapp in the TOPLEFT dropdown. then have a look at all available
events for appXL in the TOPRIGHT dropdown.

I've found this a very effective way of coding. As there's only one set
of code you dont need to worry about version etc.


Note on debugging/testing: if you reset your code (stateloss)..
the appXL variable is lost, so you'll have to reinit it.

'Code for ThisWorkbook
Option Explicit

Dim WithEvents XLapp As Application

Private Sub Workbook_Open()
'hook into the applications events
Set XLapp = Application
End Sub

Private Sub XLapp_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.Range)

If Not Sh.Parent.Name Like "myAppData*" Then Exit Sub
'this event will be triggered very often.
'make sure you write it ULTRA efficiently.
Debug.Print Target.Address(external:=True)
End Sub


Have fun...


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >