Consulting

Results 1 to 6 of 6

Thread: vba workbook_sheetchange event for xlam thisworkbook

  1. #1

    vba workbook_sheetchange event for xlam thisworkbook

    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.

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Probably just my thick noggin, but are you wanting to capture a change to the addin's sheet(s), or the active workbook?

  3. #3
    in a active workbook only.

  4. #4
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    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.

  5. #5
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location

  6. #6
    Appreciate your solution was helped me lot for my defect.

    Thanks again your support and prompt reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •