VB code to write VB code

[MeisterConrad]

Okay, So I stole this code from somewhere, and I’m trying to decipher it so I can modify it to my needs:

‘ Code to insert:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox class="Hello World"
End Sub
 
‘ into a new sheet in a new workbook
Sub Sample()
Dim wb As Workbook, ws class=As Worksheet
Dim VBP As =Object, VBC As =Object, CM As =Object
Dim strProcName =As String
 
Set wb = Workbooks.Add
Set ws = wb.Sheets(1)
 
Set VBP = wb.VBProject
Set VBC = VBP.VBComponents(ws.Name)
Set CM = VBC.CodeModule
 
strProcName = "Worksheet_SelectionChange"
 
With wb.VBProject.VBComponents( _wb.Worksheets(ws.Name).CodeName).CodeModule
InsertLines Line:=.CreateEventProc("SelectionChange", "Worksheet") + 1, _
     String:=vbCrLf & _"    Msgbox ""Hello World"""
End With
End Sub

I have a Macro that includes the creation of a new Workbook. In that new Workbook, Sheets 10 through 39 are all identical in that each one contains, respectively, Tables 5 through 34, which are also identical, located in the same cell in each Sheet. I would like to also include some VB code to be written into each of those sheets when they’re being created by the Macro.

So, I think, because my use involves ‘THIS’ Workbook and ‘THIS’ WorkSheet, I could simply change lines 10 & 11, above, to

Set wb = ThisWorkbook

Set ws = ThisWorksheet

And my use should fit in just fine. …What else do I gotta do?...
Well, I’ve located the point in MY Macro where I would want to insert this new code. At the beginning of the Macro, I’ve set n as an Integer that serves as a counter. So, the Worksheets into which I want to add code begin with Table10.
When n=n+1, i.e., when we've created another new worksheet, "“Table” & (n+6)" adjusts accordingly.
So, I could Set ws = wb.Sheets(“Table” & (n+6)) , but “ThisWorksheet” is probably just as good or easier.

I, kind of, understand that Lines 12 through 14 are dealing with the terms that identify/focus on the VB aspect of things, and I’m guessing that Line 16 specifies that it is, in fact, VB code for this Worksheet that is what is to be inserted , and Line 17 carries out that process, and inbetween the quotation marks at the end of Line 17 is where I would insert the code that I want attached to that Worksheet.

I have no idea what Line 17 “String:=vbCrLf & _” means.

I also don’t understand Line 15. Since I’ve already added the Worksheet to which I’m attaching code, do I even need Line 15 at all? …and how does that affect Line 17?


Recap: MY Macro already creates a new Worksheet. To have MY Macro add code to the current/selected/active Worksheet, how much of this stolen code do I really need?


I know, I know,... why don't I just make a template worksheet and copy/paste?
I might just end up doing that, but, for now, I'm trying a li'l something, and I'd like to see if it can be done this way.
Thanks, in advance for the help!

 

[James006]

Hi,

If you need "Programming the VBA Editor", just dive into Chip Pearson's VBE BRILLIANT explanation :

Programming In The VBA Editor

www.cpearson.com