Can VBA write VBA code into Modules or elsewhere

[billbrunt]

Hi -

I want to be able to write VBA which writes VBA code into the view code piece of a new worksheet. For example, I've got some code I'd like to include in

Private Sub Worksheet_Change(ByVal Target As Range)

The worksheet this would be incluided in would be dynamically created by some VBA code. So, once the worksheet is created, I'd then have my VBA routine write some VBA into the Worksheet_Change.

How can I do this?

- Bill

 

[Blade Hunter]

In the past I have had a blank sheet in my host macro which contains my Worksheet_Change macro. When you start your new workbook, copy the blank sheet from the host and it will take the macros with it.

 

[usenethelp]

You can do something like:

Sub AddCode()

Dim strCode As String

strCode = "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf
strCode = strCode & "msgbox target.value" & vbCrLf
strCode = strCode & "End Sub"

With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
.InsertLines .CountOfLines + 1, strCode
End With
End Sub

 

[billbrunt]

Mr. UseNetHelp --

Wow....perfect, just what I needed.

Thank you so much!

- Bill