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?