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