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.
Appreciate your solution was helped me lot for my defect.
Thanks again your support and prompt reply.
Check this
http://www.cpearson.com/excel/AppEvent.aspx