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!

'Creating the object (In a different module)
Dim MZ As New MZWorkSheet
Set MZ = ThisWorkbook.MZWorksheet.Add(After:=Worksheets("Raw Data"))
MZ.Name = "MZ Sheet"
MZ.Visible = True
[...]






'MZWorksheet Class Module code
Public WithEvents aSheet As Worksheet


Property Set Sheet(sht As Worksheet)
Set aSheet = sht
End Property


Private Sub aSheet_Change(ByVal Target As Range)
'See if the flag for the MZ has been set; otherwise, do nothing.
[...]
End Sub

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.

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":

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?

Thanks, Roy,

Sorry I hadn't been tagging them. I promise I had read the rules beforehand but I had forgotten in my haste. : P

Cheers!

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.

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.