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:

Please Login or Register  to view this content.

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).