CodeModule utilization problem

[Valeria]

Dear experts,
I have a VBA code in workbook "A" that writes VBA code in workbook "B".
The code looks like:

With ActiveWorkbook.VBProject.VBComponents("Sheet4").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
Etc.

It works on putting the code in Sheet4, but as Sheet4 is a created worksheet
by another macro, it is not always the sheet where I want the code!
Is there a way that I can tell VBA to put the code in the sheet called
"Data" instead than on Sheet4?

Thank you very much for your help.
Best regards
Valeria

 

[Jim Rech]

It's easy to get the sheet's code module from its worksheet name:

Worksheets("Data").CodeName

--
Jim
| Dear experts,
| I have a VBA code in workbook "A" that writes VBA code in workbook "B".
| The code looks like:
|
| With ActiveWorkbook.VBProject.VBComponents("Sheet4").CodeModule
| StartLine = .CreateEventProc("Change", "Worksheet") + 1
| .InsertLines StartLine, _
| Etc.
|
| It works on putting the code in Sheet4, but as Sheet4 is a created
worksheet
| by another macro, it is not always the sheet where I want the code!
| Is there a way that I can tell VBA to put the code in the sheet called
| "Data" instead than on Sheet4?
|
| Thank you very much for your help.
| Best regards
| Valeria
|

 

[Valeria]

Hi,
I have tried this:

With ActiveWorkbook.Worksheets("Data").CodeName
StartLine = .CreateEventProc("Change", "Worksheet") + 1
etc.

But I get the error "object required"...
Thanks for helping me!
Kind regards

 

[Chip Pearson]

You need something like

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents( _
ActiveWorkbook.Worksheets("Sheet3").CodeName).CodeModule
StartLine = .CreateEventProc("Change", "Worksheet")
End With



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

sName = ActiveWorkbook.Worksheets("Data").CodeName
With ActiveWorkbook.VBProject.VBComponents(sName).CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _

 

[Valeria]

This works wonderfully, thank you very much!
Best regards
Valeria