Help.. Add an event to a sheet module

R

Ray Mills

I am dying here...I am trying to add an event to a sheet module
I tested the code below which adds an open event to ThisWorkbook module
and works great. "The CodeModule object has a method called
CreateEventProc that you can use to create an event procedure in class
module, a sheet"
but i just cant figure how change the "ThisWorkbook" to a sheet moule

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
StartLine = .CreateEventProc("Open", "Workbook") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
 
D

Don Guillett

Could be because a sheet event does not have an OPEN. It has an activate.
See if this helps.

Sub addevent()
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet3").CodeModule
StartLine = .CreateEventProc("selectionchange", "Worksheet") + 1
.InsertLines StartLine, _
"Msgbox ""Hello World"",vbOkOnly"
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top