Writing code into a Module via VBA & VBE

[VenturSum]

Team,

When I add a new worksheet, I must also copy to that sheet a command button. Next I need to add some code to the sheet's VBE object. And need some help here.

I've added the code to a string variable

sString = "Private Sub cboInsertText_Click()" & vbcrlf & _
          "Call InsertText" & vbcrlf & _
          "Sub End"

I've already changed the sheet's name. But I don't know what the sheet's "codename" is. So I need to address the VBE Object by the sheets' tab name.

Any Ideas??

John,
In Annapolis.

 

[pgc01]

Hi John

Try this:

Sub AddWs_Code()
Dim wsh As Worksheet
Dim CodeM As CodeModule
Dim sString As String
 
sString = "Private Sub cboInsertText_Click()" & vbCrLf & _
          "Call InsertText" & vbCrLf & _
          "End Sub"
 
Set wsh = ActiveWorkbook.Worksheets.Add(After:=Sheets(Sheets.Count))
wsh.Name = "MyWorksheet"
 
Set CodeM = ActiveWorkbook.VBProject.VBComponents(wsh.CodeName).CodeModule
CodeM.InsertLines CodeM.CountOfLines + 1, sString
End Sub

Remark: I set the reference to "Microsoft Visual Basic for Applications Extensibility 5.3" to be able to use early binding.

If you don't set the reference, use

Dim CodeM As Object

 

[VenturSum]

PGC,

Thank you very much for this post.
The client uses Excel 2000,
so I'll have to see which extension library is on his machine.

Most Gratefully,

John,
In Annapolis