Add worksheet change event to all sheets during runtime_new

[Ashley91]

Hello,

So I am trying to work on this again now. My code works if I do it per sheet, but the number of sheets differ monthly so I can't do the per sheet code. How do I loop through all sheets and add the worksheet_change event to each sheet?

What am I doing wrong in the below code?


With wTarget
For Each sh In Worksheets
Set xPro = .VBProject
Set xCom5 = xPro.VBComponents(sh)
Set xMod = xCom5.CodeModule


With xMod
xLine = .CreateEventProc("Change", "Worksheet")
xLine = xLine + 1
.InsertLines xLine, " End If"
.InsertLines xLine, " Next cll"
.InsertLines xLine, " Cells(cll.Row, ""Q"") = Join(Array(Cells(cll.Row, ""D"").Text, Cells(cll.Row, ""H"").Text, Cells(cll.Row, ""M"").Text, Cells(Target.Row, ""N"").Text, Cells(cll.Row, ""O"").Text, Cells(cll.Row, ""P"").Text), vbLf)"
.InsertLines xLine, " For Each cll In myRng"
.InsertLines xLine, " If Not myRng Is Nothing Then"
.InsertLines xLine, " Set myRng = Intersect(Target, Range(""D:D,H:H,M:P""))"
End With
Next sh
End With

Please help thank you so much

[p45cal]

Why don't you put code in the ThisWorkbook module and forget it:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "NameOfASheetNotToBeProcessed" Then 'you can use any conditional statement here to exclude/include sheets; this is just an example.
With Sh
Set myRng = Intersect(Target, .Range("D:D,H:H,M:P"))
If Not myRng Is Nothing Then
For Each cll In myRng
.Cells(cll.Row, "Q") = Join(Array(.Cells(cll.Row, "D").Text, .Cells(cll.Row, "H").Text, .Cells(cll.Row, "M").Text, .Cells(Target.Row, "N").Text, .Cells(cll.Row, "O").Text, .Cells(cll.Row, "P").Text), vbLf)
Next cll
End If
End With
End If
End Sub

[snb]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each it in Intersect(Target, sh.Range("D:D,H:H,M:P")
sn = sh.usedrange.rows(it.row)
sh.Cells(it.Row,17) = Join(Array(sn(1,4), sn(1,8), sn(1,13), sn(1,14), sn(1,15), sn(1,16)), vbLf)
Next cll
End Sub

[Ashley91]

Hello p45cal and snb, I need to add the codes programmatically. So what happens is on button click it will create a new workbook with n number of sheets (where n is equal to the number of weeks in the current month), then after that it needs to add the worksheet_change event to each sheet and save it with a specific filename. I am done with everything else except for the add the worksheet_change event to each sheet. I am also open to workbook sheet change event but I am not sure how to add parameters to xLine = .CreateEventProc("Change", "Worksheet") part :confused3:confused4

[snb]

See
https://www.snb-vba.eu/VBA_Excel_VBproject_en.html#L_3.3.3.5
(https://www.snb-vba.eu/VBA_Excel_VBproject_en.html#L_3.3.3.5)

But why programmatically ?
Create a workbook, containing the eventcode and use it as a template.

[p45cal]

Create a workbook, containing the eventcode and use it as a template.Agreed.

[Ashley91]

See
https://www.snb-vba.eu/VBA_Excel_VBproject_en.html#L_3.3.3.5
(https://www.snb-vba.eu/VBA_Excel_VBproject_en.html#L_3.3.3.5)

But why programmatically ?
Create a workbook, containing the eventcode and use it as a template.

Hello, it was requested by my teammates before I leave the company