macro to create code for sheets

Hi,

I have a form that creates custom sheets dynamically as the user needs them. The problem that I have is that I need to add two sub procedures associated with each sheet, BeforeDoubleClick and SelectionChange.
When the form creates the sheet I need it to add the following code as well:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call sheetDoubleClick("Day")
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsEmpty(ActiveCell) <> True Or ActiveCell.Column <> "3" Then
Range("C" & ActiveCell.Row).Select
End If
End Sub

How would I do this from a macro??

Any help would be great

Ben

See Chip Pearson's site - Programming To The Visual Basic Editor.

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

I was able to revise code there:

Sub AddProcedureToSheet()

Dim VBCodeMod As CodeModule
Dim LineNum As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
With VBCodeMod
    LineNum = .CountOfLines + 1
    .InsertLines LineNum, _
"Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)" & Chr(13) & _
" Call sheetDoubleClick(""Day"")" & Chr(13) & _
"End Sub" & Chr(13) & _
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & Chr(13) & _
" If IsEmpty(ActiveCell) <> True Or ActiveCell.Column <> ""3"" Then" & Chr(13) & _
" Range(""C"" & ActiveCell.Row).Select" & Chr(13) & _
" End If" & Chr(13) & _
"End Sub"
End With

End Sub

Thanks heaps that worked well....just have one more issue. With this line:

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule

If my sheet name is say "test". If I put that instead of Sheet1 I get subscript out of range.

It is listed as Sheet1(test) under mircosoft excel objects. How do i either find out what sheet is attached to a sheet name or reference that name of the sheet

If you run the code immediately after adding the sheet, it should be the activesheet.

Try replacing ("Sheet1") with (ActiveSheet.Name).