Adding events to Worksheet Class Module

  • Hello, all,


    I've been reading the forums, but I can't seem to find anything that is specifically relevant to what I'm trying to do.

    What I'm trying to write is code for a class module which is triggered whenever a value is changed on a worksheet using the Worksheet_Change event which compares two values on the sheet. It seems to work when stepping through, but it doesn't get triggered at all when control is given back to the User.

    When I try declaring the new worksheet as an "MZWorksheet" which would have that Worksheet_Change event property, even with the WithEvents statement, it doesn't recognize "MZWorksheet.Add"

    If there's another, better way of doing this, please let me know!


    Edited once, last by royUK: added code tags, please read the Forum Rules to understand their importance. ().

  • Your code would need to be something like:


    Code
    Set MZ.aSheet = ThisWorkbook.Worksheets.Add(After:=Worksheets("Raw Data"))

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Hi, Rory,


    Thanks! That helped, but now it doesn't seem to recognize MZ.Name as a property of the MZWorksheet. Do I need to manually add every property of the "Worksheet" Excel type to the "MZWorksheet" Class Module?


    Thanks again.

  • Either that or use the aSheet property to return the worksheet. Better yet, add Set and Get properties to assign and retrieve the worksheet.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Hi again, Rory,


    Thanks for your help! I think I understood what you were saying, but the aSheet_Change event is still not being triggered when the User has the ability to edit the page again. I believe I successfully made MZ an MZWorksheet and MZ.aSheet Set to a new Worksheet added after the sheet "Raw Data":


    Code
    Dim MZ As New MZWorkSheet
    Set MZ.aSheet = ThisWorkbook.Worksheets.Add(After:=Worksheets("Raw Data"))
    MZ.aSheet.Name = "MZ Sheet"
    MZ.aSheet.Visible = True


    The aSheet_Change event is triggered when the MZ sheet is being modified with data while the vba script is running, but once it has finished, any edits I make to the sheet will not trigger the event.


    I'm sorry if I'm being confusing or if I don't understand fundamentally what the issue is. :/

    Thank you, anyways!

  • Basically, if I copy and paste the aSheet_Change event that I made into the sheet's code, it'll run, but I want to be able to create a sheet with this code from scratch every time because we delete the sheet to remove all data/formatting (Cells.Clear doesn't seem to work correctly if you're trying to pull the last row/column after). I was trying to treat the mzWorksheet as a Class and create an object (a new worksheet) that has that aSheet_Change event as one of its Procedures/Class Methods.


    Does that make sense?

  • Hi, all,


    I have temporarily found a solution in adding code to the workbook programmatically (Adding code during runtime). Using the .InsertLines method.

    I'm not a huge fan of it, because it looks terrible, but if anyone has better ways of adding code to a sheet procedurally, I would be happy to hear it.


    Thanks again for everyone's work.

  • Can't you use a template sheet to copy or the SheetChange event of the WorkBook Module.


    Personally, I would have a hidden template sheet that you copy as a new sheet.

  • Roy,


    I hadn't thought of that! That's a good idea. I can protect that sheet from deletion and edits, so I can just copy that new one, etc, so I think that'll work out well.

    Was the original idea that I had (using a class module) not the correct time/application for the problem presented?


    Thank you for your assistance, once again.

  • I would use some VBA code like this



  • I suspect something you are doing in your original code is causing state loss, so the project resets and your variable is cleared, which means no more events are trapped.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!