CODEWITHSUNDEEP
vbaexcel
CodeCamper
CodeCamper

Reputation: 6984

Change events dynamically?

How do you programatically set events to a workbook. I have seen workbooks that have things such as ThisWorkbook.OnClose = "macronamehere" and this is very useful but I can't seem to replicate this in my Excel 2013 workbooks.

Summary: How do you set events to a specific macro using only the VBA language rather than the GUI drop down.

Reason: I would like to be able to do it this way because then it will also explain how I can create a workbook in VBA code and directly code the events to that newly created workbook as well as dynamically change my events in a different way.

Upvotes: 1

Views: 96

Answers (1)

basodre
basodre

Reputation: 5770

Can you employ Class Modules in order to achieve the desired result? I'll include an example below to give an idea of how they would work.

First, add a Class Module (Insert - Class Module), and enter the following code:

Private WithEvents xlApp As Application

Private Sub Class_Initialize()
    Set xlApp = Application
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    Cancel = True
    MsgBox ("I will remember you!")
End Sub

Next, we have to instantiate the new class, which we could do in a standard module. The code is as follows (note, Class1 is just the default name for a class, which you can, and should, change in the Class Properties Menu):

Private xlAppInstance As Class1

Sub LoadClass()
    Set xlAppInstance = New Class1
End Sub

Upvotes: 2

Related Questions