Custom Workbook Events

Goal = I'm trying to create reusable code to handle custom events at a workbook level.

Ideally I would like to make this so that I can add it to existing Excel macro projects/tools (be it XLSM or XLAM) and only minimal changes are required to make the code work with that project.

(Optional background info - I have several projects that use custom events. Most of these created by me but their custom events were unique to each project. I am now trying to consolidate their code to create a reusable/template class or module that they can each use.)


At this stage, I am using three objects:

1. ThisWorkbook
2. A module to contain all the custom events called from Item 1
3. A module to contain the functions & global variables that are used by Item 2 but their values are unique to that project

The point of keeping Item 2 separate to Item 1 is to reduce clutter in ThisWorkbook class. Also to store all the subprocedures/functions that the custom events require.
The point of keeping Item 3 separate to Item 2 is that whenever I update Item 2, I can copy/paste it to any other project already using this code without needing to make additional edits.

So starting with Item 1 (i.e. the code inside ThisWorkbook class). Below is what I am currently using: (I will show/look at Items 2 & 3 after Item 1 is sorted out)

'/ Last updated 20190819 1343
'/  20190814 1438

Option Explicit

Private mblnThisAddInJustInstalled As Boolean

''/ Comment out if not using Custom Ribbon
'Private pRibbonUI As IRibbonUI
'
'Public Property Let ribbonUI(ByRef iRib As IRibbonUI)
''/ Set RibbonUI to property for later use
''/ Comment out if not using Custom Ribbon
'    Set pRibbonUI = iRib
'End Property
'
'Public Property Get ribbonUI() As IRibbonUI
''/ Retrieve RibbonUI from property for use
''/ Comment out if not using Custom Ribbon
'    Set ribbonUI = pRibbonUI
'End Property

Private Sub Workbook_AddinInstall()
'/ This event followed by Workbook_Open
    mblnThisAddInJustInstalled = True
End Sub

Private Sub Workbook_Open()
    Call CustomEvent_TWB_Open(mblnThisAddInJustInstalled)
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' cancel the inbuilt save
    Cancel = True
    ' replace with custom save
    Call CustomEvent_TWB_BeforeSave(SaveAsUI)
End Sub

Private Sub Workbook_AddinUninstall()
'/ This event followed by Workbook_BeforeClose
    mblnThisAddInJustInstalled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim blnRequireCustomSave As Boolean

    If ThisWorkbook.Saved Then
        GoTo ExitProcedure
    End If

    If Not ThisWorkbook.IsAddin Then
        ' if using 'Force Enable Macros' code in XLSM do custom SaveAndClose
        If LenB(gstrcWS_ENABLE_MACROS) Then
            blnRequireCustomSave = True
        End If
    Else
        ' if closing XLAM, remind developer to save
        If LenB(gstrcDEV_MATCH_NAME) Then
            blnRequireCustomSave = InStr(1, Application.UserName, gstrcDEV_MATCH_NAME, vbTextCompare)
        End If
    End If

    If blnRequireCustomSave Then
        Do
            ' custom Save&Close event
            Select Case MsgBox("Do you want to save the changes you made to '" & ThisWorkbook.Name & "'?", vbQuestion + vbYesNoCancel)
            Case vbYes
                Call CustomEvent_TWB_BeforeSave
            Case vbNo
                ThisWorkbook.Saved = True
            Case vbCancel
                ' user chose Cancel so the workbook close is also cancelled
                Cancel = True
                Exit Sub
            End Select
        Loop Until ThisWorkbook.Saved = True
    End If

ExitProcedure:
    Call CustomEvent_TWB_BeforeClose
End Sub

1. COMMENT: This code is not 100% reusable as 1 variable and 2 properties need to be commented out if the project isn't using custom ribbon code. (Most of my projects already are. I see no need to change this code - it already notes the need to comment out that part of the code if that particular project is not using)
2. COMMENT: The code is calling procedures that are not shown e.g. CustomEvent_TWB_BeforeSave. These are kept in Item 2. I will show these once Item 1 concepts are sorted out.
3. COMMENT: Workbook_BeforeClose - The code references global constants that are not shown. These are kept in Item 3. I will show these once Item 1 concepts are sorted out.
4. QUESTION: Workbook_BeforeClose - I have doubts as the Custom Save event code I have inside this function. Shouldn't the Workbook_BeforeSave trigger automatically whenever Workbook_BeforeClose is triggered and the XLSM/XLAM has property
   

   ThisWorkbook.Saved = False

   ?
5. QUESTION: Workbook_AddinUninstall - Is there any point setting this variable back to false? Would it not be impossible to fire Workbook_Open event for that add-in after it has been uninstalled UNLESS the user reinstalls the add-in in the same Excel session? (In which case the Workbook_AddInInstall would set the variable back to True anyway)
6. QUESTION: Is there anything you have noticed that you think should be changed/added?

Hi mc84,

It looks like there is a way to create custom events in VBA. Read about them at:
https://docs.microsoft.com/en-us/off...vent-statement
Or
https://stackoverflow.com/questions/...ithevents-part

Marv

Hi Marv, Thanks for the suggestion. My questions are not so much asking "How" to run custom events but rather focusing more on what we are doing with custom events and why.

Hey,

I think the first line in my last suggestion has an example of what you would use a RaiseEvent call for. I wish there were more examples of custom events.

Here is another site that uses Events on Charts.
https://powerspreadsheets.com/excel-vba-events/
I think this site explains many more places where Events might be used.

Hi Kyle, good to hear from you again

Originally Posted by Kyle123

Before we start, are you using actual custom events or Subs/Functions that you are referring to as “events”.

I am using the event procedures in the ThisWorkbook module * to call my custom Subs.

Put another way - I am redirecting the actual event macros in TWB module by having them call my custom macros. As an example, here is one of the procedures from ThisWorkbook class/module

Private Sub Workbook_Open()
    Call CustomEvent_TWB_Open(mblnThisAddInJustInstalled)
End Sub

* Note I usually refer to this as the 'ThisWorkbook class' - as I mentally classify this as being a type of class. Stop me if that is confusing!

Originally Posted by Kyle123

I suspect the latter based on your description and code, if so are you wedded to this approach or are you prepared to use actual events?

Well I'm wouldn't say I am wedded to my current approach, no. I am open to the idea of changing over to a custom class and WithEvents if that is a better approach.

Originally Posted by Kyle123

It will make a significant difference to any code/approach

Agreed. Correct me if I am wrong, but would the differences be limited to how we start/trigger the custom events and where they are stored? What we actually decide to do in the custom event itself would be the same regardless of which approach used?

I found that if I cleared the tab, removed instructions and saved it. Then re-entered it exactly as before, I got my image back.