Macro to write code INTO the VBE

[malik641]

I was wondering how I can take a macro to copy a sheet and paste/insert a new one into the same workbook and then assigning code to the worksheet itself (as a Private Sub Worksheet_Change event).

The hard part for me is assigning code to the new sheet

Any ideas???

[Bob Phillips]

I was wondering how I can take a macro to copy a sheet and paste/insert a new one into the same workbook and then assigning code to the worksheet itself (as a Private Sub Worksheet_Change event).

The hard part for me is assigning code to the new sheet

Any ideas???

Here is an example

Sub AddWorksheetEventProc()
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        StartLine = .CreateEventProc("Change", "Worksheet") + 1
        .InsertLines StartLine, _
        "Dim ans" & vbCrLf & _
        "   ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
        "   If ans = vbNo Then Cancel = True"
    End With
End Sub

[malik641]

Thanks for the example XLD, I just have a couple more questions.

Using your example, I've put this together (Still Incomplete):

Sub AddWorksheetEventProc()
Dim StartLine As Long
Dim WSName As String
'Sheet "Joe" is used as the template---WSName will be the name of the
'copied worksheet
WSName = Application.InputBox("What is the Sheet Name?", Type:=2)
If WSName = "" Then
MsgBox "Sheet will not be created. There is no name for it.", _
vbOKOnly + vbExclamation, "No new sheet"
Exit Sub
End If
'This line works with Excel 2003, but not with Excel 2000...What's the syntax?
'And how do I set the WSName to the new sheet??
Sheets("Joe").Copy 'Before:=Sheets("Joe)
With ActiveWorkbook.VBProject.VBComponents(WSName.CodeName).CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With
End Sub

How do you set the name for the new worksheet? And How do I fix that line that sorts the new sheet before "Joe" for the correct Excel 2000 syntax?

[Bob Phillips]

Sub AddWorksheetEventProc()
    Dim StartLine As Long
    Dim WSName As String
     'Sheet "Joe" is used as the template---WSName will be the name of the
     'copied worksheet
    WSName = Application.InputBox("What is the Sheet Name?", Type:=2)
    If WSName = "" Then
        MsgBox "Sheet will not be created. There is no name for it.", _
        vbOKOnly + vbExclamation, "No new sheet"
        Exit Sub
    End If
    Worksheets("Joe").Copy Before:=Worksheets("Joe")
    ActiveSheet.Name = WSName
    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
        StartLine = .CreateEventProc("Change", "Worksheet") + 1
        .InsertLines StartLine, _
        "Dim ans" & vbCrLf & _
        " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
        " If ans = vbNo Then Cancel = True"
    End With
End Sub

[malik641]

That works pretty well XLD, good stuff

Just curious, if I didn't want to write all the code that I have to into the new sheet, could I just import the code from the template to the new sheet? If so, how can I do that?

Thanks again.
BTW, I'm pretty sure that'll be the last question for this thread.

[malik641]

Wait a minute...Nevermind. The code will copy itself...Awesome

That's all I needed. Thanks again XLD. You pulled through yet again

[Bob Phillips]

That works pretty well XLD, good stuff

Just curious, if I didn't want to write all the code that I have to into the new sheet, could I just import the code from the template to the new sheet? If so, how can I do that?

Thanks again.
BTW, I'm pretty sure that'll be the last question for this thread.

Normally I would suggest exporting and importing the module, but that doesn't work with class modules (and worksheets are they).

I would suggest copying the code to a text file and add it to that

With ActiveworkBook1.VBProject.VBComponents(Worksheets(WSName).CodeName)
    .CodeModule.AddFromFile "C:\Text1.txt"
End With

[malik641]

I would suggest copying the code to a text file and add it to that

With ActiveworkBook1.VBProject.VBComponents(Worksheets(WSName).CodeName)
.CodeModule.AddFromFile "C:\Text1.txt"
End With

That wouldn't work too well in my case. The file would have to be on a DataShare, and I don't know if the department this is for has one setup with this specific workbook. And I'm sure IT wouldn't create a DataShare for just one .txt document.

But thanks for the code, I'm definitely keeping that for future reference