When I insert a sheet in my workbook, there must be (simple) PrivateSub
added to that Sheet. Can that be done?

http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy


"Gert-Jan" wrote:

When I insert a sheet in my workbook, there must be (simple) PrivateSub
added to that Sheet. Can that be done?

It can be done.

Example
( in ThisWorkbook code module):

Const SheetCode As String = _
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbCrLf
& _
"Call MySelectionChange(Target)" & vbCrLf & _
"End Sub"

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim comp As VBComponent
Dim LineNum As Long


With ThisWorkbook.VBProject.VBComponents(Sh.CodeName)
If .type = vbext_ct_Document Then
With .CodeModule
LineNum = .CountOfLines + 1
.InsertLines LineNum, SheetCode
End With
End If
End With

End Sub

__________________________________________________ _______________________

To gain access to this class go to the Tools menu in the VBA editor,
choose the References item, and put a check next to "Microsoft Visual
Basic For Applications Extensibility" library.

If you need more details:I got this wisdom he
http://www.cpearson.com/excel/vbe.htm

Met vriendelijke groeten,

Lex