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
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