PDA

View Full Version : [SOLVED] Macro to write code INTO the VBE



malik641
09-20-2005, 06:11 AM
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 http://vbaexpress.com/forum/images/smilies/102.gif

Any ideas???

Bob Phillips
09-20-2005, 06:34 AM
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 http://vbaexpress.com/forum/images/smilies/102.gif

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
09-20-2005, 07:49 AM
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
09-20-2005, 07:57 AM
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
09-20-2005, 08:08 AM
That works pretty well XLD, good stuff :clap:

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
09-20-2005, 08:18 AM
Wait a minute...Nevermind. The code will copy itself...Awesome :yes

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

Bob Phillips
09-20-2005, 08:32 AM
That works pretty well XLD, good stuff :clap:

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
09-20-2005, 09:31 AM
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 :thumb