Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home





CONCLUSION: No one knows about EVENT CLASS proccedures

Posted by JohnG on January 30, 2002 12:58 PM
See SheetChange Event.

Check out our Excel VBA Resources

Re: CONCLUSION: No one knows about EVENT CLASS proccedures

Posted by Joe Was on January 30, 2002 3:23 PM
Events are very touchy when it comes to syntax, try:

Declare your event module:

Public WithEvents App As Application

Then, you must connect the declared object in the class module for the Application.

Like this:

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

Then you can use it like this:

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

'Your code here.

End Sub

Hope this Helps. JSW


Copy of your note:
Have you any ideas as to why the sheet change event is not working? I have a before close and before save events and they are fine. Below is an extract of the code I am using.

--In AppEventClass
Public WithEvents Appl As Application

Private Sub Appl_Workbook_SheetChange(ByVal Sh As
Object, ByVal Source As Range)

Dim ApplicationClass As New AppEventClass ' Creates variable to be used to activate events.

--In Thisworkbook
Private Sub Workbook_Open()
Set ApplicationClass.Appl = Application 'Actives all events created in AppEventClass.
End Sub


Re: CONCLUSION: No one knows about EVENT CLASS proccedures

Posted by JohnG on January 31, 2002 5:41 AM
Well whats happened is the beforesave and beforeclose that I also use work its just the sheetchange that does not.

: See SheetChange Event. Copy of your note: Have you any ideas as to why the sheet change event is not working? I have a before close and before save events and they are fine. Below is an extract of the code I am using. Public WithEvents Appl As Application Object, ByVal Source As Range) Private Sub Workbook_Open() Set ApplicationClass.Appl = Application 'Actives all events created in AppEventClass. End Sub


Re: CONCLUSION: No one knows about EVENT CLASS proccedures

Posted by Ivan F Moala on January 31, 2002 9:14 AM

Private Sub Appl_Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)

is not a valid Class Event !
should be.......

Private Sub Appl_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox Sh.Name & "Changed"
End Sub

This goes in your Class module and is applicable
to ALL workbooks


Ivan


Re: CONCLUSION: No one knows about EVENT CLASS proccedures

Posted by JohnG on January 31, 2002 12:15 PM
Private Sub Appl_Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) should be....... MsgBox Sh.Name & "Changed" End Sub to ALL workbooks Ivan


Re: CONCLUSION: No one knows about EVENT CLASS proccedures

Posted by JohnG on January 31, 2002 12:16 PM
It is created as a new class in a new class module. Private Sub Appl_Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) should be....... MsgBox Sh.Name & "Changed" End Sub to ALL workbooks Ivan


Re: CONCLUSION: No one knows about EVENT CLASS proccedures

Posted by Juan Pablo G. on January 31, 2002 1:44 PM
Are you sure you know what you're doing ?

Ivan is correct.

Juan Pablo G. It is created as a new class in a new class module. : : Private Sub Appl_Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) : is not a valid Class Event ! : should be....... : Private Sub Appl_SheetChange(ByVal Sh As Object, ByVal Target As Range) : MsgBox Sh.Name & "Changed" : End Sub : This goes in your Class module and is applicable : to ALL workbooks : : Ivan


Re: CONCLUSION: No one knows about EVENT CLASS proccedures

Posted by Ivan F Moala on January 31, 2002 1:53 PM
There's no need to create a new class for the
workbook_sheetchange event (besides you would have to program
the classes events and properties)...just use
the current class you have defined. If you need
to check a specific workbook/Sheet for the change event
then reference it in you current class as


Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox App.ActiveWorkbook.Name & " " & Sh.Name & " Changed"
If App.ActiveWorkbook.Name = "Book1" And Sh.Name = "Sheet1" Then
MsgBox "I've changed " & App.ActiveWorkbook.Name & " " & _
Sh.Name & Target.Address & " Changed"
End If
End sub

If in the Class module you click on the right
pane you will see a list of the Valid events
for this class you cannot add to this unless
you create your own objects, properties, collections etc. Defining the

Private Sub Appl_Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

you defined is NOT a valid event for this class.


Ivan It is created as a new class in a new class module. : : Private Sub Appl_Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) : is not a valid Class Event ! : should be....... : Private Sub Appl_SheetChange(ByVal Sh As Object, ByVal Target As Range) : MsgBox Sh.Name & "Changed" : End Sub : This goes in your Class module and is applicable : to ALL workbooks : : Ivan


Re: CONCLUSION: No one knows about EVENT CLASS proccedures

Posted by JohnG on January 31, 2002 2:08 PM
This created as a new class so that it can effect any work book not just the workbook it is stored in.
I have a BeforeSave and BeforeClose created as new class events and they work fine. Are you sure you know what you're doing ? Ivan is correct. Juan Pablo G. : It is created as a new class in a new class module.




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.