VBE code in a loop

L

loloflores

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top