Inserting code for worksheet object event

[cram]

Hi all,
I'm trying to use a macro to automatically insert code for a worksheet event (worksheet selection change). I was think
along the lines of the following below. Any thoughts?


Dim wks1 As Worksheet

Sheets("Data&quot.Select
Set wks1 = ThisWorkbook.ActiveSheet

wks1.CodeModule.AddFromString "Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)" & vbCrLf & vbTab & _
"SMain2" & vbCrLf & _
"End Sub"
Range("A1&quot.Select


Any input would be appreciated.
thx,
Cram

 

[SkipVought]

Why would you be doing this in code? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[cram]

Hi Skip,
I'm writing a script which creates a new file (which I will be running numerous times), and the output file needs the worksheet event code. I guess I could just paste the code every time I need to, but I was just looking to avoid an extra step.
thx,
Cram

 

[SkipVought]

Think of one sheet as your template sheet that has hour event code in it.

Write some code to Copy that sheet to a new workbook.

Now your new workbook has the event code. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RobBroekhuis]

Skip's suggestion is a good one, probably the most appropriate in this particular case. In general, the approach you suggested in your original post is perfectly valid. Did you have a problem getting it to work?
The object reference needs to be changed a bit:

activeworkbook.VBProject.VBComponents(activesheet.name).CodeModule.addfromstring _
"Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)" & vbCrLf & vbTab & _
"SMain2" & vbCrLf & _
"End Sub"
Rob

 

[ctbaker]

Hi Rob,

I am trying to utilize your code and am getting a "Subscript out of range" error but I have no idea why. My project is to build some combo boxes after a user clicks on a button. Then I want to assign some code to one of the combo boxes after it is created. For the example line of code I am assuming it is ComboBox3.

ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule.AddFromString "Sub Worksheet_ComboBox3_Change(ByVal Target As Excel.Range)" & vbCrLf & vbTab & "Call SetFundName" & vbCrLf & "End Sub"

Thanks,
Chad

 

[cg084]

Hi All,

I am using a similar method.

ActiveWorkbook.VBProject.VBComponents(Sheets(1).Name).CodeModule.addfromstring _
"Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf & vbTab & _
"MyRow = Target.Row" & vbCrLf & _
"Cells(MyRow, 22).Value = Date" & vbCrLf & _
"End Sub"

I am trying to add this sub to a sheet rather than a module. I am also getting the "Subscript out of Range" error.

I have tried to add the sub to a blank module and it works.

I hope someone can help.
cg084

 

[cg084]

Hi All,

I have managed to figure it out. This wont work cause it wants to put the code in a module. So replace Sheets(1).Name with "Module1" it will need to exist before hand though.

ActiveWorkbook.VBProject.VBComponents(Sheets(1).Name).CodeModule.addfromstring _
"Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf & vbTab & _
"MyRow = Target.Row" & vbCrLf & _
"Cells(MyRow, 22).Value = Date" & vbCrLf & _
"End Sub"

To add an event to a sheet this should work.

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"MyRow = Target.Row" & vbCrLf & _
"Cells(MyRow, 22).Value = Date"
End With

Where Worksheet references the worksheet and change is the event. In this case it would mean the Worksheet_Change event.

Any problems please let me know.