Hi,
Is there any way to write "workbook_sheetchange" event for Addin xlam Modules or thisworkbook.
Appriciate if you can provide a solution.
Thanks,
Divakar.
Hi,
Is there any way to write "workbook_sheetchange" event for Addin xlam Modules or thisworkbook.
Appriciate if you can provide a solution.
Thanks,
Divakar.
Probably just my thick noggin, but are you wanting to capture a change to the addin's sheet(s), or the active workbook?
in a active workbook only.
Thank you for your answer. I am too tired to be writing this, but by the most simple example, you want to create Application level events. VERY MINIMALLY TESTED, but with no other add-ins, in the add-in...
Create a Class Module named 'clsApplicationEvents'
In clsApplicationEvents:
[vba]Option Explicit
Private WithEvents oApp As Excel.Application
Property Set XL(Application As Excel.Application)
Set oApp = Application
End Property
Property Get XL() As Excel.Application
Set XL = oApp
End Property
Private Sub oApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "A change was made in " & Sh.Parent.Name & "|" & Sh.Name
End Sub[/vba]
In a Standard Module:
[vba]Option Explicit
Global AnyWorkbook As clsApplicationEvents
[/vba]
In ThisWorkBook Module:
[vba]Option Explicit
Private Sub Workbook_Open()
Set AnyWorkbook = New clsApplicationEvents
Set AnyWorkbook.XL = Excel.Application
End Sub[/vba]
In essence, you want the AddIn to grab a reference to the Application upon opening, and therefore, be able to get Events tied in.
Check this
http://www.cpearson.com/excel/AppEvent.aspx
Appreciate your solution was helped me lot for my defect.
Thanks again your support and prompt reply.