ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   VBE code in a loop (https://www.excelbanter.com/excel-programming/296842-vbe-code-loop.html)

loloflores

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





All times are GMT +1. The time now is 09:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com