VBE code in a loop
I have a set of macros that crates new sheets from a "template sheet".
The amount of created sheets depends on the user input, so it is not
known in advance. I want to add to those sheets some event code.
To that end I try to use a modified Chip Pearson code and it woks
perfectly when run "by hand", but refuses to work when call from a loop
For example if I have 3 sheets name Solut.#1,Solut.#2 and Solut.#3:
Sub newtest()
Application.EnableEvents = False
For I = 1 To 3
xxx = "Solut.#" & I
With ActiveWorkbook.VBProject.VBComponents( _
ActiveWorkbook.Sheets(xxx).CodeName).CodeModule
StartLine = .CreateEventProc("SelectionChange", "Worksheet") + 1
.InsertLines StartLine, "Msgbox ""Hello!!"",vbOkOnly"
End With
Next I
Application.EnableEvents = True
End Sub
kills excel. I tried in different systems on both xl2000 and xl2002.
but the following runs without problem
Sub test()
Application.EnableEvents = False
With ActiveWorkbook.VBProject.VBComponents( _
ActiveWorkbook.Sheets("Calib.Solut.#1").CodeName). CodeModule
StartLine = .CreateEventProc("SelectionChange", "Worksheet") + 1
.InsertLines StartLine, "Msgbox ""Hello!!"",vbOkOnly"
End With
Application.EnableEvents = True
End Sub
Can someone there enlighten me?
Thanks in advance for any help.
Lolo Flores
|