Excel

How to use Triggered Events for all Workbooks with an Add-In

Ease of Use

Hard

Version tested with

2002 

Submitted by:

Jacob Hilderbrand

Description:

This is an example of how to create a class of every workbook that you have open. You can then use triggered events in each workbook with an Add-In. 

Discussion:

There are Worksheet and Workbook level events, but there is no built in Application level event. In some cases you may want to use a Triggered Event in every Workbook with an Add-In. This is an example of how to do that. 

Code:

instructions for use 

			
 '*** Create a new workbook and put this code in the ThisWorkbook code module. ***
Option Explicit 
 
Private Sub Workbook_Open() 
     
    Call ClassInitialize 
     
End Sub 
 
 '*** Insert a Standard Module and put in this code. ***
Option Explicit 
 
Dim Wkbs()          As New WorkBookClass 
Dim App             As New App 
 
Public Sub ClassInitialize() 
     
    Dim i               As Long 
    Dim Wkb             As Workbook 
     
    For Each Wkb In Application.Workbooks 
        i = i + 1 
        ReDim Preserve Wkbs(1 To i) 
        Set Wkbs(i).WkbBook = Wkb 
    Next 
    Set App.App = Application 
     
End Sub 
 
 '*** Insert a Class Module and rename it to App. Then put in this code. ***
Option Explicit 
 
Public WithEvents App As Application 
 
Private Sub App_NewWorkbook(ByVal Wb As Workbook) 
     
    Call ClassInitialize 
     
End Sub 
 
 '*** Create a Class Module and rename it to WorkBookClass. Then put in this code. ***
Option Explicit 
 
Public WithEvents WkbBook As Workbook 
 
Private Sub WkbBook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
     
    MsgBox "Triggers are working properly:" & vbNewLine & _ 
    "Workbook Name: " & WkbBook.Name & vbNewLine & _ 
    "Worksheet Name: " & Sh.Name & vbNewLine & _ 
    "Range Address: " & Target.Address(False, False) 
     
End Sub

How to use:

  1. Open Excel.
  2. Alt + F11 to open the VBE.
  3. Double Click on the ThisWorkbook code module and add the appropriate code from above.
  4. Insert a Standard Module and add the appropriate code from above.
  5. Insert two Class Modules and name them App and WorkBookClass and add the appropriate code from above. Make sure that the Class Modules are named correctly and that the correct code is put into each one.
  6. Close the VBE (Alt + Q or press the X in the top right corner).
  7. Save the workbook as an Add-In.
  8. Close the workbook.
 

Test the code:

  1. Open a workbook.
  2. Tools | Add-Ins...
  3. Browse for and install the Add-In.
  4. Type something into a cell and the code will trigger.
  5. Add a new workbook and type something into a cell in the new workbook and the code will still trigger.
 

Sample File:

AppWorkbookWorksheetEvent.ZIP 8.65KB 

Approved by mdmackillop


This entry has been viewed 188 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express