PDA

View Full Version : vba workbook_sheetchange event for xlam thisworkbook



divakarganta
10-22-2012, 03:52 AM
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.

GTO
10-22-2012, 04:20 AM
Probably just my thick noggin, but are you wanting to capture a change to the addin's sheet(s), or the active workbook?

divakarganta
10-22-2012, 04:24 AM
in a active workbook only.

GTO
10-22-2012, 05:40 AM
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:

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

In a Standard Module:

Option Explicit

Global AnyWorkbook As clsApplicationEvents


In ThisWorkBook Module:

Option Explicit

Private Sub Workbook_Open()
Set AnyWorkbook = New clsApplicationEvents
Set AnyWorkbook.XL = Excel.Application
End Sub

In essence, you want the AddIn to grab a reference to the Application upon opening, and therefore, be able to get Events tied in.

mohanvijay
10-22-2012, 05:58 AM
Check this

http://www.cpearson.com/excel/AppEvent.aspx

divakarganta
10-23-2012, 03:18 AM
Appreciate your solution was helped me lot for my defect.

Thanks again your support and prompt reply.:cool: :cool: