Add worksheet change event to all sheets during runtime

[Ashley91]

Hello, so what I am trying to achieve is, on button click the file will create a new workbook and add n number of sheets and n number of tables in each sheet, which is already done thanks to p45cal and arnelgp's help. And then on each of those sheets, the code provided by p45cal or arnelgp (again) here http://www.vbaexpress.com/forum/showthread.php?69344-Concatenate-columns-without-formula-in-private-sub-worksheet_change

I found this on a different site:


Sub AddCode()
Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim code As String: code = "Sub Duh()" & vbCrLf & " debug.print(""I'm Here!"")" & vbCrLf & "End Sub"


Dim lineCount As Integer


With wb.VBProject.VBComponents(ws.Name).CodeModule
lineCount = .CountOfLines
If lineCount > 0 Then
.DeleteLines 1, lineCount
End If
.AddFromString code
End With
End Sub




Which works however if I change the sheet name to my sheet name (example 15 minutes Inter - 1st Week Nov), it's giving an error saying Subscript out of range. But that's impossible because my sheet is named exactly like that and I am running the code above on that same workbook, unless I am misunderstanding what that error means. :banghead:

Also there will be 4 or 5 sheets and the code above will only add the worksheet change event to just the indicated sheet and I need the code be added to all the sheets in that workbook :banghead:

I am using Excel 365. Thanks to anyone who will help enlighten me :help

[p45cal]

is wb the new workbook?

[p45cal]

Hang on.. if you're using arnelgp's code which I think is copying a range over and over from a template sheet, then instead of copying the range to a new newly added blank sheet in a (new) workbook, you make a new sheet by copying the whole template sheet (then further added tables on that sheet can be created by only copying a range (disable events first)), any code behind the sheet being copied from the template workbook will go with it automatically.
I haven't time just now to look at the code - perhaps ask arnelgp?

[Paul_Hossler]

I think you're confusing the worksheet .Name (15 minutes Inter - 1st Week Nov) and it's .CodeName (Sheet1), which you can change BTW

29142



Option Explicit


Sub AddCode()
Dim ws As Worksheet: Set ws = Sheet1 ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim wb As Workbook: Set wb = ActiveWorkbook
Dim code As String: code = "Sub Duh()" & vbCrLf & " debug.print(""I'm Here!"")" & vbCrLf & "End Sub"

With wb.VBProject.VBComponents(ws.CodeName).CodeModule ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
If .CountOfLines > 0 Then .DeleteLines 1, .CountOfLines
.AddFromString code
End With
End Sub

[Ashley91]

Hey guys, I haven't been able to check your replies as I had an accident last Friday. I am still recovering. I will try this out as soon as I go back to work. I really appreciate the time you all are giving me. :)

[arnelgp]

get well soon:(
in ph or elsewhere, which region in ph?