Worksheet-specific event handling needed
In the Sheet1 code, I have the following:
-------------------------------------------------------- Option Explicit Dim shtClass As New EventClass Dim appClass As New EventClass Private WithEvents evtTest As EventClass Private Sub Worksheet_Activate() ' The below statements when activated do nothing - no events are triggered in EventClass ' Dim shtClass As New EventClass ' Dim appClass As New EventClass ' this permits an event raised in the Class to be trapped here Set evtTest = New EventClass ' the below does nothing Set shtClass.Ws = Application.ActiveSheet ' Two separate event triggers can be established Set shtClass.Sht = Application Set appClass.App = Application Call evtTest.RaiseIt ' this is executed first, before the Application events End Sub Private Sub evtTest_testEvent(strMsg As String) MsgBox ("testEvent proc triggered with message:" & vbCrLf & strMsg) End Sub ----------------------------------------------------------------------------------------------------- In a class module called EventClass is this: Option Explicit Public WithEvents Ws As Worksheet Public WithEvents App As Application Public WithEvents Sht As Application Public Event testEvent(strMsg As String) Private Sub App_NewWorkbook(ByVal Wb As Excel.Workbook) MsgBox "Application Event: New Workbook: " & Wb.Name End Sub Private Sub App_SheetActivate(ByVal Sh As Object) MsgBox "Application Event: SheetActivate: " & Sh.Name End Sub Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook) MsgBox "Application Event: WorkbookOpen: " & Wb.Name End Sub Private Sub Sht_SheetActivate(ByVal Sh As Object) MsgBox "Sheet Event: SheetActivate: " & Sh.Name ' RaiseIt THIS CALL DOES NOT WORK - MUST BE CALLED FROM OUTSIDE OF CLASS EXPLICTLY ' The below call does not work either ' Dim evt As EventClass ' Set evt = New EventClass ' evt.RaiseIt End Sub Public Sub RaiseIt() RaiseEvent testEvent("Raised event from EventClass RaiseIt Method") End Sub Private Sub Ws_SheetActivate(ByVal Sh As Object) MsgBox ("WS-Sheet Activation occurred") End Sub --------------------------------------------------------------------------------------------------------------------------------- There are two problems. First, I discovered I cannot raise a custom event from within a class module....it just doesn't fire. Works fine when called from another module or sheet or whatever. Secondly, the above code fires the class methods xxx_SheetActivate(....) for ALL WORKSHEETS, despite the fact the code is only in Sheet1. How can this be modified to ONLY fire the class methods for Sheet1 without having to hard-code a If-then-else condition ? In other words, how can it be done by redefining the class properly to only cause events to occur at Sheet1 ? |
Worksheet-specific event handling needed
I'm not clear what you are trying to do here.
Also your nomenclature is a little confusing. e.g.: Set shtClass.Sht = Application Set appClass.App = Application I tend to use property Let/Set/Get instead of Public variable so you can trace the code into the class modules. Not sure what you mean by: ' RaiseIt THIS CALL DOES NOT WORK - MUST BE CALLED FROM OUTSIDE OF CLASS EXPLICTLY ' The below call does not work either ' Dim evt As EventClass ' Set evt = New EventClass ' evt.RaiseIt If you want to raise the event then do the same as in the other routine: RaiseEvent testEvent("Raised event from EventClass RaiseIt Method") Explain more what you are trying to achieve. NickHK "syswizard" wrote in message ps.com... In the Sheet1 code, I have the following: -------------------------------------------------------- Option Explicit Dim shtClass As New EventClass Dim appClass As New EventClass Private WithEvents evtTest As EventClass Private Sub Worksheet_Activate() ' The below statements when activated do nothing - no events are triggered in EventClass ' Dim shtClass As New EventClass ' Dim appClass As New EventClass ' this permits an event raised in the Class to be trapped here Set evtTest = New EventClass ' the below does nothing Set shtClass.Ws = Application.ActiveSheet ' Two separate event triggers can be established Set shtClass.Sht = Application Set appClass.App = Application Call evtTest.RaiseIt ' this is executed first, before the Application events End Sub Private Sub evtTest_testEvent(strMsg As String) MsgBox ("testEvent proc triggered with message:" & vbCrLf & strMsg) End Sub -------------------------------------------------------------------------- --------------------------- In a class module called EventClass is this: Option Explicit Public WithEvents Ws As Worksheet Public WithEvents App As Application Public WithEvents Sht As Application Public Event testEvent(strMsg As String) Private Sub App_NewWorkbook(ByVal Wb As Excel.Workbook) MsgBox "Application Event: New Workbook: " & Wb.Name End Sub Private Sub App_SheetActivate(ByVal Sh As Object) MsgBox "Application Event: SheetActivate: " & Sh.Name End Sub Private Sub App_WorkbookOpen(ByVal Wb As Excel.Workbook) MsgBox "Application Event: WorkbookOpen: " & Wb.Name End Sub Private Sub Sht_SheetActivate(ByVal Sh As Object) MsgBox "Sheet Event: SheetActivate: " & Sh.Name ' RaiseIt THIS CALL DOES NOT WORK - MUST BE CALLED FROM OUTSIDE OF CLASS EXPLICTLY ' The below call does not work either ' Dim evt As EventClass ' Set evt = New EventClass ' evt.RaiseIt End Sub Public Sub RaiseIt() RaiseEvent testEvent("Raised event from EventClass RaiseIt Method") End Sub Private Sub Ws_SheetActivate(ByVal Sh As Object) MsgBox ("WS-Sheet Activation occurred") End Sub -------------------------------------------------------------------------- ------------------------------------------------------- There are two problems. First, I discovered I cannot raise a custom event from within a class module....it just doesn't fire. Works fine when called from another module or sheet or whatever. Secondly, the above code fires the class methods xxx_SheetActivate(....) for ALL WORKSHEETS, despite the fact the code is only in Sheet1. How can this be modified to ONLY fire the class methods for Sheet1 without having to hard-code a If-then-else condition ? In other words, how can it be done by redefining the class properly to only cause events to occur at Sheet1 ? |
Worksheet-specific event handling needed
On Mar 9, 1:23 am, "NickHK" wrote:
I'm not clear what you are trying to do here. Also your nomenclature is a little confusing. e.g.: Set shtClass.Sht = Application Set appClass.App = Application I tend to use property Let/Set/Get instead of Public variable so you can trace the code into the class modules. Not sure what you mean by: ' RaiseIt THIS CALL DOES NOT WORK - MUST BE CALLED FROM OUTSIDE OF CLASS EXPLICTLY ' The below call does not work either ' Dim evt As EventClass ' Set evt = New EventClass ' evt.RaiseIt Explain more what you are trying to achieve. NickHK "syswizard" wrote in message Thanks Nick....what I learned from 4 hours on this, is the following: 1) Application triggered event procedures within a Class cannot Raise events in that same class. At least I could not get it to work. 2) Application-level event procedure names are different than Worksheet event procedure names...and no debugger in the world will help you discover this...the events just won't "fire". Example: Worksheet_Activate vs. _Activate. The first is application level, the second is worksheet level. 3) I was finally able to get a SHEET specific version of this event code to work so that Activate would only be triggered for Sheet1, but alas, without the ability to create a parameterized CLASS Initializer, code must be copied if it is needed in more than one sheet. Otherwise, you have to promote the event-firing to the Application level which will hit "all sheets". BUT....If you do that, then you must then "trap" the event procedure triggered (Worksheet_Activate for instance)and only permit it to continue execution for the specific sheet or sheets of interest. If you've got 100 sheets in the workbook, it will check ALL OF THEM to determine which one has been Activated; this is very inefficent. Whew. I learned a lot about Excel VBA's class limitations and event firing. |
Worksheet-specific event handling needed
1) Application triggered event procedures within a Class cannot Raise events in that same class. Not true. For example, create a new standard module named Module1 and two class modules, Class1 and Class2. In Class1, use '''''''''''''''''''''''''''''''''''''''''''''' Public WithEvents App As Application Public Event OnNew(Wb As Workbook) Private Sub App_NewWorkbook(ByVal Wb As Workbook) RaiseEvent OnNew(Wb) End Sub '''''''''''''''''''''''''''''''''''''''''''''' In Class2, use '''''''''''''''''''''''''''''''''''''''''''''' Public WithEvents Class1EVT As Class1 Private Sub Class1EVT_OnNew(Wb As Workbook) MsgBox "Class1EVT_OnNew event in Class2" & _ "triggered by Event OnNew in Class1" End Sub '''''''''''''''''''''''''''''''''''''''''''''' In Module1 '''''''''''''''''''''''''''''''''''''''''''''' Dim C1 As Class1 Dim C2 As Class2 Sub RunTest() Set C1 = New Class1 Set C1.App = Application Set C2 = New Class2 Set C2.Class1EVT = C1 Workbooks.Add End Sub '''''''''''''''''''''''''''''''''''''''''''''' Run the code RunTest. You'll get a message box from the OnNew event in Class2. This event is defined in Class1 and raised by the Application event App_NewWorkbook in Class1 using RaiseEvent. Thus, Class1 can sink Application events and also create events of its own, even raising events in the App's event procedure. No problems at all. 2) Application-level event procedure names are different than Worksheet event procedure names...and no debugger in the world will help you discover this...the events just won't "fire". Example: Worksheet_Activate vs. _Activate. The first is application level, the second is worksheet level. and no debugger in the world will help you discover this Not True. Learn to use and love the Object Browser (F2 in VBA). The Object Browser will list all the events, their names, and their procedure declarations for the Application Object or any other object that raises events. When working with event, you will find your life MUCH easier if you use the event code that VBA will generates for you. This ensures the proper event name and declaration. In an object module such as a Class module, click the left-hand dropdown at the top of the code window, and choose the object whose events you are interested. You must declare a variable of that object type in your code using WithEvents. The name of the variable doesn't matter. The right-hand dropdown will list all of the events for that object. Select the event name in the right-hand dropdown, and VBA will insert the proper declaration for that event. Don't change the code that VBA generates for you. Worksheet_Activate vs. _Activate. The first is application level, the second is worksheet level. "Worksheet_Activate" is NOT an application event, and the second is nothing at all. "Worksheet_Activate" is an worksheet level event. The only object that has an event name "*_Activate" is a worksheet. The application event for a sheet activate is "App_SheetActivate" where "App" is the name of the variable (of data type Application) that you declared using WithEvents. E.g., Public WithEvents App As Application The variable name "App" can be anything you want. Its name is part of the event procedure name of the application event. Therefore, you could use Public WithEvents MyExcelApp As Application and your application events will have names like MyExcelApp_SheetActivate 3) I was finally able to get a SHEET specific version of this event code to work so that Activate would only be triggered for Sheet1, but alas, without the ability to create a parameterized CLASS Initializer, code must be copied if it is needed in more than one sheet. Again, not True. The ThisWorkbook module has an event named "Workbook_SheetActivate". This will fire when any sheet in the workbook is activated. This event is passed an object that references the sheet that is being activated. If you have the same code in each worksheet object module for the worksheets, you can take it out of the Worksheet object module and use "Workbook_SheetActivate". If the action of the code depends on what worksheet is being activated, test the name of Sh object passed into "Workbook_SheetActivate". This event handles all activation events for all sheets in the workbook. If you are copying the same code into each Worksheet's object module, you're doing things wrong. Use the"Workbook_SheetActivate" event for all worksheets. sheet or sheets of interest. If you've got 100 sheets in the workbook, it will check ALL OF THEM to determine which one has been Activated; this is very inefficent. Yet again, not true. The workbook SheetActivate tells you exactly which sheet is being activated. Use the workbook SheetActivate event if you have the same of similar code for the activation of a sheet. If the code is significanly different for differents, the use the Sheet's Activate event. If you need events only for a few particular worksheets, you can create WithEvents objects in the ThisWorkbook object module and you will receive event trigger only for those worksheets. E.g, in ThisWorkbook, use '''''''''''''''''''''''''''''''''''''''''''''' Public WithEvents SheetOne As Worksheet Public WithEvents SheetTwo As Worksheet Private Sub SheetOne_Activate() MsgBox "Activate: " & SheetOne.Name End Sub Private Sub SheetTwo_Activate() MsgBox "Activate: " & SheetTwo.Name End Sub '''''''''''''''''''''''''''''''''''''''''''''' You can initialize the SheetOne and SheetTwo objects either from code in a regular code module, e.g, Sub SetupSheets() Set ThisWorkbook.SheetOne = Worksheets("Sheet1") Set ThisWorkbook.SheetTwo = Worksheets("Sheet2") End Sub or in the ThisWorkbook's Open event: Private Sub Workbook_Open() Set Me.SheetOne = ThisWorkbook.Worksheets("Sheet1") Set Me.SheetTwo = ThisWorkbook.Worksheets("Sheet2") End Sub Whew. I learned a lot about Excel VBA's class limitations and event firing. I think you have a very long way to go to understand classes and events. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "syswizard" wrote in message ps.com... On Mar 9, 1:23 am, "NickHK" wrote: I'm not clear what you are trying to do here. Also your nomenclature is a little confusing. e.g.: Set shtClass.Sht = Application Set appClass.App = Application I tend to use property Let/Set/Get instead of Public variable so you can trace the code into the class modules. Not sure what you mean by: ' RaiseIt THIS CALL DOES NOT WORK - MUST BE CALLED FROM OUTSIDE OF CLASS EXPLICTLY ' The below call does not work either ' Dim evt As EventClass ' Set evt = New EventClass ' evt.RaiseIt Explain more what you are trying to achieve. NickHK "syswizard" wrote in message Thanks Nick....what I learned from 4 hours on this, is the following: 1) Application triggered event procedures within a Class cannot Raise events in that same class. At least I could not get it to work. 2) Application-level event procedure names are different than Worksheet event procedure names...and no debugger in the world will help you discover this...the events just won't "fire". Example: Worksheet_Activate vs. _Activate. The first is application level, the second is worksheet level. 3) I was finally able to get a SHEET specific version of this event code to work so that Activate would only be triggered for Sheet1, but alas, without the ability to create a parameterized CLASS Initializer, code must be copied if it is needed in more than one sheet. Otherwise, you have to promote the event-firing to the Application level which will hit "all sheets". BUT....If you do that, then you must then "trap" the event procedure triggered (Worksheet_Activate for instance)and only permit it to continue execution for the specific sheet or sheets of interest. If you've got 100 sheets in the workbook, it will check ALL OF THEM to determine which one has been Activated; this is very inefficent. Whew. I learned a lot about Excel VBA's class limitations and event firing. |
Worksheet-specific event handling needed
On Mar 9, 6:18 pm, "Chip Pearson" wrote:
Thanks much Chip for the examples and clarification; no question, you are right on my lacking the indepth experience you possess. However, I've seen very little in the way of detailed documentation and sample code regarding classes and events in the VBA books I've read. |
All times are GMT +1. The time now is 03:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com