delete vbcode dynamically
 

How to delete vbcode (coded in ThisWorkbook) of targetworkbook from
activeworkbook dynamically ??


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=379273

delete vbcode dynamically
 

be aware that that many users have DISABLED
"Trust Access to Visual Basic Project".

if not enabled then all reading/writing to VBProject will fail.
if enabled then:

With Workbooks("myTarget").VBProject. _
VBComponents("thisworkbook").CodeModule
.DeleteLines 1, .CountOfLines
End With


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ilyaskazi wrote :


How to delete vbcode (coded in ThisWorkbook) of targetworkbook from
activeworkbook dynamically ??

delete vbcode dynamically
 

Sub DeleteProcedure()
Dim oVBCodeMod As Object

Set oVBCodeMod =
Workbooks("Book1").VBProject.VBComponents("Thiswor kbook").CodeModule
With oVBCodeMod
.DeleteLines 1, .countofLines
End With

End Sub


--
HTH

Bob Phillips

"ilyaskazi" wrote
in message ...

How to delete vbcode (coded in ThisWorkbook) of targetworkbook from
activeworkbook dynamically ??


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile:
http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=379273

delete vbcode dynamically
 

wow.... perfect. thanku..

Also i m trying to put some vbcode dynamically in target wrkbk as given
below...
but compile error msg pops-up for:


Code:
--------------------

With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
.InsertLines .CountOfLines + 1, _
"Private Sub Workbook_BeforeClose(Cancel As Boolean)" & Chr(13) & _
"Dim lRowIndex As Long" & vbNewLine & _
"Dim lColIndex As Integer" & vbNewLine & _
"' RTA, OTA" & vbNewLine & _
"With Worksheets("Rules")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 9).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 16 To 19" & vbNewLine & _
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "0"" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"End If" & vbNewLine & _
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"With Worksheets("Validity")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 17).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 17 To 38" & vbNewLine &_
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
"' do nothing" & vbNewLine & _
"Else" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "'" & .Cells(lRowIndex, lColIndex).value" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"If .Cells(lRowIndex, 40).value < "" Then .Cells(lRowIndex, 40).value = "'" & .Cells(lRowIndex, 40).value" & vbNewLine & _
"If .Cells(lRowIndex, 41).value < "" Then .Cells(lRowIndex, 41).value = "'" & .Cells(lRowIndex, 41).value" & vbNewLine & _
"If .Cells(lRowIndex, 43).value < "" Then .Cells(lRowIndex, 43).value = "'" & .Cells(lRowIndex, 43).value" & vbNewLine & _
"If .Cells(lRowIndex, 44).value < "" Then .Cells(lRowIndex, 44).value = "'" & .Cells(lRowIndex, 44).value" & vbNewLine & _
"If .Cells(lRowIndex, 46).value < "" Then .Cells(lRowIndex, 46).value = "'" & .Cells(lRowIndex, 46).value" & vbNewLine & _
"If .Cells(lRowIndex, 47).value < "" Then .Cells(lRowIndex, 47).value = "'" & .Cells(lRowIndex, 47).value" & vbNewLine & _
"If .Cells(lRowIndex, 49).value < "" Then .Cells(lRowIndex, 48).value = "'" & .Cells(lRowIndex, 49).value" & vbNewLine & _
"If .Cells(lRowIndex, 50).value < "" Then .Cells(lRowIndex, 50).value = "'" & .Cells(lRowIndex, 50).value" & vbNewLine & _
"If .Cells(lRowIndex, 52).value < "" Then .Cells(lRowIndex, 52).value = "'" & .Cells(lRowIndex, 52).value" & vbNewLine & _
"If .Cells(lRowIndex, 53).value < "" Then .Cells(lRowIndex, 53).value = "'" & .Cells(lRowIndex, 53).value" & vbNewLine & _
"If .Cells(lRowIndex, 55).value < "" Then .Cells(lRowIndex, 55).value = "'" & .Cells(lRowIndex, 55).value" & vbNewLine & _
"If .Cells(lRowIndex, 56).value < "" Then .Cells(lRowIndex, 56).value = "'" & .Cells(lRowIndex, 56).value" & vbNewLine & _
"If .Cells(lRowIndex, 58).value < "" Then .Cells(lRowIndex, 58).value = "'" & .Cells(lRowIndex, 58).value" & vbNewLine & _
"If .Cells(lRowIndex, 59).value < "" Then .Cells(lRowIndex, 59).value = "'" & .Cells(lRowIndex, 59).value" & vbNewLine & _
"If .Cells(lRowIndex, 61).value < "" Then .Cells(lRowIndex, 61).value = "'" & .Cells(lRowIndex, 61).value" & vbNewLine & _
"If .Cells(lRowIndex, 62).value < "" Then .Cells(lRowIndex, 62).value = "'" & .Cells(lRowIndex, 62).value" & vbNewLine & _
"If .Cells(lRowIndex, 64).value < "" Then .Cells(lRowIndex, 64).value = "'" & .Cells(lRowIndex, 64).value" & vbNewLine & _
"If .Cells(lRowIndex, 65).value < "" Then .Cells(lRowIndex, 65).value = "'" & .Cells(lRowIndex, 65).value" & vbNewLine & _
"End If" & vbNewLine &_
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"End Sub"
End With

--------------------


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=379273

delete vbcode dynamically
 

afaik you cant/shouldnt create event procs like that.
you need to use CreateEventProc method
from Extensibility library.

chip pearson has more info on his site.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ilyaskazi wrote :


wow.... perfect. thanku..

Also i m trying to put some vbcode dynamically in target wrkbk as
given below...
but compile error msg pops-up for:


Code:
--------------------

With
ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
.InsertLines .CountOfLines + 1, _ "Private Sub
Workbook_BeforeClose(Cancel As Boolean)" & Chr(13) & _ "Dim
lRowIndex As Long" & vbNewLine & _ "Dim lColIndex As Integer" &
vbNewLine & _ "' RTA, OTA" & vbNewLine & _
"With Worksheets("Rules")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 9).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 16 To 19" & vbNewLine & _
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "0"" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"End If" & vbNewLine & _
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"With Worksheets("Validity")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 17).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 17 To 38" & vbNewLine &_
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
"' do nothing" & vbNewLine & _
"Else" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "'" & .Cells(lRowIndex,
lColIndex).value" & vbNewLine & _ "End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"If .Cells(lRowIndex, 40).value < "" Then .Cells(lRowIndex,
40).value = "'" & .Cells(lRowIndex, 40).value" & vbNewLine & _ "If
.Cells(lRowIndex, 41).value < "" Then .Cells(lRowIndex, 41).value =
"'" & .Cells(lRowIndex, 41).value" & vbNewLine & _ "If
.Cells(lRowIndex, 43).value < "" Then .Cells(lRowIndex, 43).value =
"'" & .Cells(lRowIndex, 43).value" & vbNewLine & _ "If
.Cells(lRowIndex, 44).value < "" Then .Cells(lRowIndex, 44).value =
"'" & .Cells(lRowIndex, 44).value" & vbNewLine & _ "If
.Cells(lRowIndex, 46).value < "" Then .Cells(lRowIndex, 46).value =
"'" & .Cells(lRowIndex, 46).value" & vbNewLine & _ "If
.Cells(lRowIndex, 47).value < "" Then .Cells(lRowIndex, 47).value =
"'" & .Cells(lRowIndex, 47).value" & vbNewLine & _ "If
.Cells(lRowIndex, 49).value < "" Then .Cells(lRowIndex, 48).value =
"'" & .Cells(lRowIndex, 49).value" & vbNewLine & _ "If
.Cells(lRowIndex, 50).value < "" Then .Cells(lRowIndex, 50).value =
"'" & .Cells(lRowIndex, 50).value" & vbNewLine & _ "If
.Cells(lRowIndex, 52).value < "" Then .Cells(lRowIndex, 52).value =
"'" & .Cells(lRowIndex, 52).value" & vbNewLine & _ "If
.Cells(lRowIndex, 53).value < "" Then .Cells(lRowIndex, 53).value =
"'" & .Cells(lRowIndex, 53).value" & vbNewLine & _ "If
.Cells(lRowIndex, 55).value < "" Then .Cells(lRowIndex, 55).value =
"'" & .Cells(lRowIndex, 55).value" & vbNewLine & _ "If
.Cells(lRowIndex, 56).value < "" Then .Cells(lRowIndex, 56).value =
"'" & .Cells(lRowIndex, 56).value" & vbNewLine & _ "If
.Cells(lRowIndex, 58).value < "" Then .Cells(lRowIndex, 58).value =
"'" & .Cells(lRowIndex, 58).value" & vbNewLine & _ "If
.Cells(lRowIndex, 59).value < "" Then .Cells(lRowIndex, 59).value =
"'" & .Cells(lRowIndex, 59).value" & vbNewLine & _ "If
.Cells(lRowIndex, 61).value < "" Then .Cells(lRowIndex, 61).value =
"'" & .Cells(lRowIndex, 61).value" & vbNewLine & _ "If
.Cells(lRowIndex, 62).value < "" Then .Cells(lRowIndex, 62).value =
"'" & .Cells(lRowIndex, 62).value" & vbNewLine & _ "If
.Cells(lRowIndex, 64).value < "" Then .Cells(lRowIndex, 64).value =
"'" & .Cells(lRowIndex, 64).value" & vbNewLine & _ "If
.Cells(lRowIndex, 65).value < "" Then .Cells(lRowIndex, 65).value =
"'" & .Cells(lRowIndex, 65).value" & vbNewLine & _ "End If" &
vbNewLine &_ "Next lRowIndex" & vbNewLine & _ "End With" &
vbNewLine & _ "End Sub" End With --------------------

delete vbcode dynamically
 

replied in VBAExpress

--
HTH

Bob Phillips

"ilyaskazi" wrote
in message ...

wow.... perfect. thanku..

Also i m trying to put some vbcode dynamically in target wrkbk as given
below...
but compile error msg pops-up for:


Code:
--------------------

With ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
.InsertLines .CountOfLines + 1, _
"Private Sub Workbook_BeforeClose(Cancel As Boolean)" & Chr(13) & _
"Dim lRowIndex As Long" & vbNewLine & _
"Dim lColIndex As Integer" & vbNewLine & _
"' RTA, OTA" & vbNewLine & _
"With Worksheets("Rules")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 9).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 16 To 19" & vbNewLine & _
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "0"" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"End If" & vbNewLine & _
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"With Worksheets("Validity")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 17).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 17 To 38" & vbNewLine &_
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
"' do nothing" & vbNewLine & _
"Else" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "'" & .Cells(lRowIndex,
lColIndex).value" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"If .Cells(lRowIndex, 40).value < "" Then .Cells(lRowIndex, 40).value =
"'" & .Cells(lRowIndex, 40).value" & vbNewLine & _
"If .Cells(lRowIndex, 41).value < "" Then .Cells(lRowIndex, 41).value =
"'" & .Cells(lRowIndex, 41).value" & vbNewLine & _
"If .Cells(lRowIndex, 43).value < "" Then .Cells(lRowIndex, 43).value =
"'" & .Cells(lRowIndex, 43).value" & vbNewLine & _
"If .Cells(lRowIndex, 44).value < "" Then .Cells(lRowIndex, 44).value =
"'" & .Cells(lRowIndex, 44).value" & vbNewLine & _
"If .Cells(lRowIndex, 46).value < "" Then .Cells(lRowIndex, 46).value =
"'" & .Cells(lRowIndex, 46).value" & vbNewLine & _
"If .Cells(lRowIndex, 47).value < "" Then .Cells(lRowIndex, 47).value =
"'" & .Cells(lRowIndex, 47).value" & vbNewLine & _
"If .Cells(lRowIndex, 49).value < "" Then .Cells(lRowIndex, 48).value =
"'" & .Cells(lRowIndex, 49).value" & vbNewLine & _
"If .Cells(lRowIndex, 50).value < "" Then .Cells(lRowIndex, 50).value =
"'" & .Cells(lRowIndex, 50).value" & vbNewLine & _
"If .Cells(lRowIndex, 52).value < "" Then .Cells(lRowIndex, 52).value =
"'" & .Cells(lRowIndex, 52).value" & vbNewLine & _
"If .Cells(lRowIndex, 53).value < "" Then .Cells(lRowIndex, 53).value =
"'" & .Cells(lRowIndex, 53).value" & vbNewLine & _
"If .Cells(lRowIndex, 55).value < "" Then .Cells(lRowIndex, 55).value =
"'" & .Cells(lRowIndex, 55).value" & vbNewLine & _
"If .Cells(lRowIndex, 56).value < "" Then .Cells(lRowIndex, 56).value =
"'" & .Cells(lRowIndex, 56).value" & vbNewLine & _
"If .Cells(lRowIndex, 58).value < "" Then .Cells(lRowIndex, 58).value =
"'" & .Cells(lRowIndex, 58).value" & vbNewLine & _
"If .Cells(lRowIndex, 59).value < "" Then .Cells(lRowIndex, 59).value =
"'" & .Cells(lRowIndex, 59).value" & vbNewLine & _
"If .Cells(lRowIndex, 61).value < "" Then .Cells(lRowIndex, 61).value =
"'" & .Cells(lRowIndex, 61).value" & vbNewLine & _
"If .Cells(lRowIndex, 62).value < "" Then .Cells(lRowIndex, 62).value =
"'" & .Cells(lRowIndex, 62).value" & vbNewLine & _
"If .Cells(lRowIndex, 64).value < "" Then .Cells(lRowIndex, 64).value =
"'" & .Cells(lRowIndex, 64).value" & vbNewLine & _
"If .Cells(lRowIndex, 65).value < "" Then .Cells(lRowIndex, 65).value =
"'" & .Cells(lRowIndex, 65).value" & vbNewLine & _
"End If" & vbNewLine &_
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"End Sub"
End With

--------------------


--
ilyaskazi
------------------------------------------------------------------------
ilyaskazi's Profile:
http://www.excelforum.com/member.php...o&userid=23969
View this thread: http://www.excelforum.com/showthread...hreadid=379273

delete vbcode dynamically
 

Shouldn't perhaps, but are you sure about can't.

I just tried this and it seems to work fine

With ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
.InsertLines .CountOfLines + 1, _
"Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)" & vbNewLine & _
"MsgBox ""hello""" & vbNewLine & _
"End Sub"

End With

Bob

"keepITcool" wrote in message
ft.com...

afaik you cant/shouldnt create event procs like that.
you need to use CreateEventProc method
from Extensibility library.

chip pearson has more info on his site.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


ilyaskazi wrote :


wow.... perfect. thanku..

Also i m trying to put some vbcode dynamically in target wrkbk as
given below...
but compile error msg pops-up for:


Code:
--------------------

With
ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
.InsertLines .CountOfLines + 1, _ "Private Sub
Workbook_BeforeClose(Cancel As Boolean)" & Chr(13) & _ "Dim
lRowIndex As Long" & vbNewLine & _ "Dim lColIndex As Integer" &
vbNewLine & _ "' RTA, OTA" & vbNewLine & _
"With Worksheets("Rules")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 9).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 16 To 19" & vbNewLine & _
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "0"" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"End If" & vbNewLine & _
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"With Worksheets("Validity")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 17).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 17 To 38" & vbNewLine &_
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
"' do nothing" & vbNewLine & _
"Else" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "'" & .Cells(lRowIndex,
lColIndex).value" & vbNewLine & _ "End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"If .Cells(lRowIndex, 40).value < "" Then .Cells(lRowIndex,
40).value = "'" & .Cells(lRowIndex, 40).value" & vbNewLine & _ "If
.Cells(lRowIndex, 41).value < "" Then .Cells(lRowIndex, 41).value =
"'" & .Cells(lRowIndex, 41).value" & vbNewLine & _ "If
.Cells(lRowIndex, 43).value < "" Then .Cells(lRowIndex, 43).value =
"'" & .Cells(lRowIndex, 43).value" & vbNewLine & _ "If
.Cells(lRowIndex, 44).value < "" Then .Cells(lRowIndex, 44).value =
"'" & .Cells(lRowIndex, 44).value" & vbNewLine & _ "If
.Cells(lRowIndex, 46).value < "" Then .Cells(lRowIndex, 46).value =
"'" & .Cells(lRowIndex, 46).value" & vbNewLine & _ "If
.Cells(lRowIndex, 47).value < "" Then .Cells(lRowIndex, 47).value =
"'" & .Cells(lRowIndex, 47).value" & vbNewLine & _ "If
.Cells(lRowIndex, 49).value < "" Then .Cells(lRowIndex, 48).value =
"'" & .Cells(lRowIndex, 49).value" & vbNewLine & _ "If
.Cells(lRowIndex, 50).value < "" Then .Cells(lRowIndex, 50).value =
"'" & .Cells(lRowIndex, 50).value" & vbNewLine & _ "If
.Cells(lRowIndex, 52).value < "" Then .Cells(lRowIndex, 52).value =
"'" & .Cells(lRowIndex, 52).value" & vbNewLine & _ "If
.Cells(lRowIndex, 53).value < "" Then .Cells(lRowIndex, 53).value =
"'" & .Cells(lRowIndex, 53).value" & vbNewLine & _ "If
.Cells(lRowIndex, 55).value < "" Then .Cells(lRowIndex, 55).value =
"'" & .Cells(lRowIndex, 55).value" & vbNewLine & _ "If
.Cells(lRowIndex, 56).value < "" Then .Cells(lRowIndex, 56).value =
"'" & .Cells(lRowIndex, 56).value" & vbNewLine & _ "If
.Cells(lRowIndex, 58).value < "" Then .Cells(lRowIndex, 58).value =
"'" & .Cells(lRowIndex, 58).value" & vbNewLine & _ "If
.Cells(lRowIndex, 59).value < "" Then .Cells(lRowIndex, 59).value =
"'" & .Cells(lRowIndex, 59).value" & vbNewLine & _ "If
.Cells(lRowIndex, 61).value < "" Then .Cells(lRowIndex, 61).value =
"'" & .Cells(lRowIndex, 61).value" & vbNewLine & _ "If
.Cells(lRowIndex, 62).value < "" Then .Cells(lRowIndex, 62).value =
"'" & .Cells(lRowIndex, 62).value" & vbNewLine & _ "If
.Cells(lRowIndex, 64).value < "" Then .Cells(lRowIndex, 64).value =
"'" & .Cells(lRowIndex, 64).value" & vbNewLine & _ "If
.Cells(lRowIndex, 65).value < "" Then .Cells(lRowIndex, 65).value =
"'" & .Cells(lRowIndex, 65).value" & vbNewLine & _ "End If" &
vbNewLine &_ "Next lRowIndex" & vbNewLine & _ "End With" &
vbNewLine & _ "End Sub" End With --------------------

delete vbcode dynamically
 

for further assistance check he

also posted: http://www.vbaexpress.com/forum/showthread.php?t=362

--
ilyaskaz
-----------------------------------------------------------------------
ilyaskazi's Profile: http://www.excelforum.com/member.php...fo&userid=2396
View this thread: http://www.excelforum.com/showthread.php?threadid=37927

delete vbcode dynamically
 

afaik should have been afair(emember) :)
re can't
you demonstrate you can..
thus proving i remembered wrongly.

re should't
maybe. i'm confused now :)

important:
the newly created events could interfere with running code.
so disable events before adding the code lines.

syntax..
I suggest OP uses a string variable. Set that. Then pump it.
makes for more readable code anyway.

cheerz!

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

Shouldn't perhaps, but are you sure about can't.

I just tried this and it seems to work fine

With
ThisWorkbook.VBProject.VBComponents("ThisWorkbook" ).CodeModule
.InsertLines .CountOfLines + 1, _ "Private Sub
Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)" & vbNewLine & _ "MsgBox ""hello""" & vbNewLine & _
"End Sub"

End With

Bob

"keepITcool" wrote in message
ft.com...

afaik you cant/shouldnt create event procs like that.
you need to use CreateEventProc method
from Extensibility library.

chip pearson has more info on his site.

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


ilyaskazi wrote :


wow.... perfect. thanku..

Also i m trying to put some vbcode dynamically in target wrkbk as
given below...
but compile error msg pops-up for:


Code:
--------------------

With
ActiveWorkbook.VBProject.VBComponents("ThisWorkboo k").CodeModule
.InsertLines .CountOfLines + 1, _ "Private Sub
Workbook_BeforeClose(Cancel As Boolean)" & Chr(13) & _ "Dim
lRowIndex As Long" & vbNewLine & _ "Dim lColIndex As Integer" &
vbNewLine & _ "' RTA, OTA" & vbNewLine & _
"With Worksheets("Rules")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 9).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 16 To 19" & vbNewLine & _
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine &
_ ".Cells(lRowIndex, lColIndex).value = "0"" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"End If" & vbNewLine & _
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"With Worksheets("Validity")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 17).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 17 To 38" & vbNewLine &_
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine &
_ "' do nothing" & vbNewLine & _
"Else" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "'" & .Cells(lRowIndex,
lColIndex).value" & vbNewLine & _ "End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"If .Cells(lRowIndex, 40).value < "" Then .Cells(lRowIndex,
40).value = "'" & .Cells(lRowIndex, 40).value" & vbNewLine & _
"If .Cells(lRowIndex, 41).value < "" Then .Cells(lRowIndex,
41).value = "'" & .Cells(lRowIndex, 41).value" & vbNewLine & _
"If .Cells(lRowIndex, 43).value < "" Then .Cells(lRowIndex,
43).value = "'" & .Cells(lRowIndex, 43).value" & vbNewLine & _
"If .Cells(lRowIndex, 44).value < "" Then .Cells(lRowIndex,
44).value = "'" & .Cells(lRowIndex, 44).value" & vbNewLine & _
"If .Cells(lRowIndex, 46).value < "" Then .Cells(lRowIndex,
46).value = "'" & .Cells(lRowIndex, 46).value" & vbNewLine & _
"If .Cells(lRowIndex, 47).value < "" Then .Cells(lRowIndex,
47).value = "'" & .Cells(lRowIndex, 47).value" & vbNewLine & _
"If .Cells(lRowIndex, 49).value < "" Then .Cells(lRowIndex,
48).value = "'" & .Cells(lRowIndex, 49).value" & vbNewLine & _
"If .Cells(lRowIndex, 50).value < "" Then .Cells(lRowIndex,
50).value = "'" & .Cells(lRowIndex, 50).value" & vbNewLine & _
"If .Cells(lRowIndex, 52).value < "" Then .Cells(lRowIndex,
52).value = "'" & .Cells(lRowIndex, 52).value" & vbNewLine & _
"If .Cells(lRowIndex, 53).value < "" Then .Cells(lRowIndex,
53).value = "'" & .Cells(lRowIndex, 53).value" & vbNewLine & _
"If .Cells(lRowIndex, 55).value < "" Then .Cells(lRowIndex,
55).value = "'" & .Cells(lRowIndex, 55).value" & vbNewLine & _
"If .Cells(lRowIndex, 56).value < "" Then .Cells(lRowIndex,
56).value = "'" & .Cells(lRowIndex, 56).value" & vbNewLine & _
"If .Cells(lRowIndex, 58).value < "" Then .Cells(lRowIndex,
58).value = "'" & .Cells(lRowIndex, 58).value" & vbNewLine & _
"If .Cells(lRowIndex, 59).value < "" Then .Cells(lRowIndex,
59).value = "'" & .Cells(lRowIndex, 59).value" & vbNewLine & _
"If .Cells(lRowIndex, 61).value < "" Then .Cells(lRowIndex,
61).value = "'" & .Cells(lRowIndex, 61).value" & vbNewLine & _
"If .Cells(lRowIndex, 62).value < "" Then .Cells(lRowIndex,
62).value = "'" & .Cells(lRowIndex, 62).value" & vbNewLine & _
"If .Cells(lRowIndex, 64).value < "" Then .Cells(lRowIndex,
64).value = "'" & .Cells(lRowIndex, 64).value" & vbNewLine & _
"If .Cells(lRowIndex, 65).value < "" Then .Cells(lRowIndex,
65).value = "'" & .Cells(lRowIndex, 65).value" & vbNewLine & _
"End If" & vbNewLine &_ "Next lRowIndex" & vbNewLine & _ "End
With" & vbNewLine & _ "End Sub" End With --------------------

delete vbcode dynamically
 

"keepITcool" wrote in message
ft.com...
re should't
maybe. i'm confused now :)

You may be confused, but I agree absolutely with you. There is a method, so
use it.

important:
the newly created events could interfere with running code.
so disable events before adding the code lines.

Excellent point!

syntax..
I suggest OP uses a string variable. Set that. Then pump it.
makes for more readable code anyway.

Agree again. That is how I do it. It is also how I create formulas in VBA,
it also makes debugging far easier.