Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > delete vbcode dynamically
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,253
Default 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 ??
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,120
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,253
Default 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 --------------------
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,120
Default 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



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,120
Default 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 --------------------


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,253
Default 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 --------------------
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,120
Default 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.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with VBcode in Excel thetoppy Excel Discussion (Misc queries) 2 April 13th 06 01:02 PM
generate sub dynamically Christian Galbavy[_2_] Excel Programming 1 March 11th 05 04:19 PM
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below Annette[_4_] Excel Programming 2 September 21st 04 02:40 PM
vbCode to Link a picture to a user form rather than embed it novicevbaer[_8_] Excel Programming 0 August 26th 04 09:07 PM
Help - where to put VBCode on a network Mervyn Thomas Excel Programming 1 January 28th 04 11:31 AM


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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement