Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop or Find code ?? need help !! | Excel Worksheet Functions | |||
loop code error help!!!! | Excel Discussion (Misc queries) | |||
Loop for VBA code? | Excel Worksheet Functions | |||
How to Loop some code | Excel Discussion (Misc queries) | |||
Loop code problems | Excel Programming |