+ Reply to Thread
Results 1 to 6 of 6

Custom Workbook Events

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    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)


    Please Login or Register  to view this content.

    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
      Please Login or Register  to view this content.
      ?
    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?
    Last edited by mc84excel; 08-23-2019 at 12:16 AM. Reason: update code to latest version
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,460

    Re: Custom Workbook Events

    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
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Custom Workbook Events

    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.
    Last edited by mc84excel; 08-23-2019 at 12:14 AM. Reason: fix incorrect meaning caused by inadvertently leaving out certain words

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,460

    Re: Custom Workbook Events

    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.

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,131

    Re: Custom Workbook Events

    Before we start, are you using actual custom events or Subs/Functions that you are referring to as “events”. 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? It will make a significant diffeeence to any code/approach

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    I design and/or assist on projects used across multiple countries. If it's region specific, please ask instead of assuming.
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Custom Workbook Events

    Hi Kyle, good to hear from you again

    Quote Originally Posted by Kyle123 View Post
    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

    Please Login or Register  to view this content.
    * 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!


    Quote Originally Posted by Kyle123 View Post
    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.


    Quote Originally Posted by Kyle123 View Post
    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1