CodeModule utilization problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
|
 
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
 
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
 
sName = ActiveWorkbook.Worksheets("Data").CodeName
With ActiveWorkbook.VBProject.VBComponents(sName).CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
 
Back
Top