How to write by program an event hander

W

Warda

Hi,
Is it possible to write by program, using VBA language, an event handler
(i.e. Workbook_SheetActivate(ByVal Sh As Object)) and to use it as an
add-ins. How can I do this. Thanks in advance.
 
X

XL-Dennis

Warda,

#1 Add a classmodule to Your VBA project
#2 Add the following on top in the classmodule:

Private WithEvents xlApp As Excel.Application

#3 In the Object combobox above the classmodule select xlApp
#4 Choose the wanted Event procedure in the Procedure combobox.
 
W

Warda

Thank you Dennis for your response.
I followed all steps you suggested me, and I got the following code in
Class1:

Private WithEvents xlApp As Excel.Application

Private Sub xlApp_SheetDeactivate(ByVal Sh As Object)
Dim I, FileName
MsgBox "It works"
I = 1
FileName = "C:\LastSheet" ' Create file name.
Open FileName For Output As #I ' Open file.
Print #I, Sh.Name ' Write string to file.
Close #I
End Sub
But this code is not executed when I move between sheets. Is there any think
missing?
Thanks for your help.

Warda.
 
X

XL-Dennis

Warda,

My apologize for my previously incomplete post.

The following code should be added to the Classmodule (In the example it has
the name of 'Class1' :

Option Explicit
Dim WithEvents xlApp As Excel.Application

Private Sub Class_Initialize()
Set xlApp = Application
End Sub

Private Sub xlApp_SheetDeactivate(ByVal Sh As Object)
MsgBox "I'm activated"
End Sub

The following code should be added in a standardmodule:
Option Explicit

Dim xlEvents As Class1

Sub Auto_Open()
Set xlEvents = New Class1
End Sub

I hope that the above gives You a better guidance.
 
Top