Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
I get a runtime error in Excel using the following method:
Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.name = Worksheets(1).codeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = wsEvent vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub What am i doing wrong? Any help appreciated ojv |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
On 4 Giu, 23:37, ojv wrote:
I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String * * For Each vbc In ActiveWorkbook.VBProject.VBComponents * * * * If vbc.name = Worksheets(1).codeName Then * * * * * * pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") * * * * * * body = wsEvent * * * * * * vbc.CodeModule.InsertLines pos, body * * * * * * Exit For * * * * End If * * Next End Sub What am i doing wrong? Any help appreciated ojv Hi ojv. Probably you forget a reference to: 'Microsoft visual basic for applications extensibility 5.x as required by Mr. "r". For further informations, see: http://www.cpearson.com/excel/vbe.aspx Regards Eliano |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
On 4 Giu, 23:37, ojv wrote:
I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String * * For Each vbc In ActiveWorkbook.VBProject.VBComponents * * * * If vbc.name = Worksheets(1).codeName Then * * * * * * pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") * * * * * * body = wsEvent * * * * * * vbc.CodeModule.InsertLines pos, body * * * * * * Exit For * * * * End If * * Next End Sub What am i doing wrong? Any help appreciated ojv Hi ojv. Probably you forget a reference to: 'Microsoft visual basic for applications extensibility 5.x as required by Mr. "r". For further informations, see: http://www.cpearson.com/excel/vbe.aspx Regards Eliano |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
Thx for responding. I already have a reference to the extensibility lib. The
runtime crash occurs after execution of pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") before the statement inserting body text. I am able to insert a number of different events like workbook events, but this one fails. I just do not understand why. ojv "r" wrote: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body As String 'with reference to 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub Sub AddEvents2() Dim vbc, pos As Long, body As String 'no reference 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.name = Worksheets(1).codeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = wsEvent vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub What am i doing wrong? Any help appreciated ojv |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
Thx for responding. I already have a reference to the extensibility lib. The
runtime crash occurs after execution of pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") before the statement inserting body text. I am able to insert a number of different events like workbook events, but this one fails. I just do not understand why. ojv "r" wrote: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body As String 'with reference to 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub Sub AddEvents2() Dim vbc, pos As Long, body As String 'no reference 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.name = Worksheets(1).codeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = wsEvent vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub What am i doing wrong? Any help appreciated ojv |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
that error you get?
number and description please regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: Thx for responding. I already have a reference to the extensibility lib. The runtime crash occurs after execution of pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") before the statement inserting body text. I am able to insert a number of different events like workbook events, but this one fails. I just do not understand why. ojv "r" wrote: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body As String 'with reference to 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub Sub AddEvents2() Dim vbc, pos As Long, body As String 'no reference 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.name = Worksheets(1).codeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = wsEvent vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub What am i doing wrong? Any help appreciated ojv |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
There are no error number or description. Excel just tells me it crashed,
needs to shut down, will restore my documents and asks the usual question of wether I want to send an error report to MS. regards ojv "r" wrote: that error you get? number and description please regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: Thx for responding. I already have a reference to the extensibility lib. The runtime crash occurs after execution of pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") before the statement inserting body text. I am able to insert a number of different events like workbook events, but this one fails. I just do not understand why. ojv "r" wrote: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body As String 'with reference to 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub Sub AddEvents2() Dim vbc, pos As Long, body As String 'no reference 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.name = Worksheets(1).codeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = wsEvent vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub What am i doing wrong? Any help appreciated ojv |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
the routine, I work regularly
what's on wsEvent regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: There are no error number or description. Excel just tells me it crashed, needs to shut down, will restore my documents and asks the usual question of wether I want to send an error report to MS. regards ojv "r" wrote: that error you get? number and description please regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: Thx for responding. I already have a reference to the extensibility lib. The runtime crash occurs after execution of pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") before the statement inserting body text. I am able to insert a number of different events like workbook events, but this one fails. I just do not understand why. ojv "r" wrote: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body As String 'with reference to 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub Sub AddEvents2() Dim vbc, pos As Long, body As String 'no reference 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.name = Worksheets(1).codeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = wsEvent vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub What am i doing wrong? Any help appreciated ojv |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
the routine, I work regularly
what's on wsEvent regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: There are no error number or description. Excel just tells me it crashed, needs to shut down, will restore my documents and asks the usual question of wether I want to send an error report to MS. regards ojv "r" wrote: that error you get? number and description please regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: Thx for responding. I already have a reference to the extensibility lib. The runtime crash occurs after execution of pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") before the statement inserting body text. I am able to insert a number of different events like workbook events, but this one fails. I just do not understand why. ojv "r" wrote: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body As String 'with reference to 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub Sub AddEvents2() Dim vbc, pos As Long, body As String 'no reference 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.name = Worksheets(1).codeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = wsEvent vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub What am i doing wrong? Any help appreciated ojv |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
I've modified such that parameter body is two lines only. When I run the code
below everything works fine inserting the Workbook_Open event. When i step through the remaining code it goes past the line LineNum = .CreateEventProc("Change", "Worksheet") and I see that Private Sub Worksheet_Change(ByVal Target As Range) End Sub is inserted in the code module for the worksheet, it is also there in the recovered workbook. VB then halts for 1-2 seconds while at the next line LineNum = LineNum +1 before it crashes displaying the send error message dialog. I'm running 2002 SP3. Regards ojv Sub AddEvents() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Dim body As String Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("ThisWorkbook") Set CodeMod = VBComp.CodeModule body = "Dim i as Integer" & vbCrLf & "i=0" With CodeMod LineNum = .CreateEventProc("Open", "Workbook") LineNum = LineNum + 1 .InsertLines LineNum, body End With Set VBComp = VBProj.VBComponents(Worksheets(1).codeName) Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CreateEventProc("Change", "Worksheet") LineNum = LineNum + 1 .InsertLines LineNum, body End With End Sub "r" wrote: the routine, I work regularly what's on wsEvent regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: There are no error number or description. Excel just tells me it crashed, needs to shut down, will restore my documents and asks the usual question of wether I want to send an error report to MS. regards ojv "r" wrote: that error you get? number and description please regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: Thx for responding. I already have a reference to the extensibility lib. The runtime crash occurs after execution of pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") before the statement inserting body text. I am able to insert a number of different events like workbook events, but this one fails. I just do not understand why. ojv "r" wrote: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body As String 'with reference to 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub Sub AddEvents2() Dim vbc, pos As Long, body As String 'no reference 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.name = Worksheets(1).codeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = wsEvent vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub What am i doing wrong? Any help appreciated ojv |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
I've modified such that parameter body is two lines only. When I run the code
below everything works fine inserting the Workbook_Open event. When i step through the remaining code it goes past the line LineNum = .CreateEventProc("Change", "Worksheet") and I see that Private Sub Worksheet_Change(ByVal Target As Range) End Sub is inserted in the code module for the worksheet, it is also there in the recovered workbook. VB then halts for 1-2 seconds while at the next line LineNum = LineNum +1 before it crashes displaying the send error message dialog. I'm running 2002 SP3. Regards ojv Sub AddEvents() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Dim body As String Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("ThisWorkbook") Set CodeMod = VBComp.CodeModule body = "Dim i as Integer" & vbCrLf & "i=0" With CodeMod LineNum = .CreateEventProc("Open", "Workbook") LineNum = LineNum + 1 .InsertLines LineNum, body End With Set VBComp = VBProj.VBComponents(Worksheets(1).codeName) Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CreateEventProc("Change", "Worksheet") LineNum = LineNum + 1 .InsertLines LineNum, body End With End Sub "r" wrote: the routine, I work regularly what's on wsEvent regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: There are no error number or description. Excel just tells me it crashed, needs to shut down, will restore my documents and asks the usual question of wether I want to send an error report to MS. regards ojv "r" wrote: that error you get? number and description please regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: Thx for responding. I already have a reference to the extensibility lib. The runtime crash occurs after execution of pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") before the statement inserting body text. I am able to insert a number of different events like workbook events, but this one fails. I just do not understand why. ojv "r" wrote: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body As String 'with reference to 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub Sub AddEvents2() Dim vbc, pos As Long, body As String 'no reference 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.name = Worksheets(1).codeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = wsEvent vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub What am i doing wrong? Any help appreciated ojv |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
I can only tell you that on my 2003 also works very well.
I am sorry not to be able to help regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: I've modified such that parameter body is two lines only. When I run the code below everything works fine inserting the Workbook_Open event. When i step through the remaining code it goes past the line LineNum = .CreateEventProc("Change", "Worksheet") and I see that Private Sub Worksheet_Change(ByVal Target As Range) End Sub is inserted in the code module for the worksheet, it is also there in the recovered workbook. VB then halts for 1-2 seconds while at the next line LineNum = LineNum +1 before it crashes displaying the send error message dialog. I'm running 2002 SP3. Regards ojv Sub AddEvents() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Dim body As String Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("ThisWorkbook") Set CodeMod = VBComp.CodeModule body = "Dim i as Integer" & vbCrLf & "i=0" With CodeMod LineNum = .CreateEventProc("Open", "Workbook") LineNum = LineNum + 1 .InsertLines LineNum, body End With Set VBComp = VBProj.VBComponents(Worksheets(1).codeName) Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CreateEventProc("Change", "Worksheet") LineNum = LineNum + 1 .InsertLines LineNum, body End With End Sub "r" wrote: the routine, I work regularly what's on wsEvent regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: There are no error number or description. Excel just tells me it crashed, needs to shut down, will restore my documents and asks the usual question of wether I want to send an error report to MS. regards ojv "r" wrote: that error you get? number and description please regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: Thx for responding. I already have a reference to the extensibility lib. The runtime crash occurs after execution of pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") before the statement inserting body text. I am able to insert a number of different events like workbook events, but this one fails. I just do not understand why. ojv "r" wrote: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body As String 'with reference to 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub Sub AddEvents2() Dim vbc, pos As Long, body As String 'no reference 'Microsoft visual basic for applications extensibility 5.x For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.Name = Worksheets(1).CodeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = "test" vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub regards r Il mio ultimo lavoro ... http://excelvba.altervista.org/blog/...ternative.html "ojv" wrote: I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.name = Worksheets(1).codeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = wsEvent vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub What am i doing wrong? Any help appreciated ojv |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
On 5 Giu, 21:21, ojv wrote:
I've modified such that parameter body is two lines only. When I run the code below everything works fine inserting the Workbook_Open event. When i step through the remaining code it goes past the line * * LineNum = .CreateEventProc("Change", "Worksheet") and I see that Private Sub Worksheet_Change(ByVal Target As Range) End Sub is inserted in the code module for the worksheet, it is also there in the recovered workbook. VB then halts for 1-2 seconds while at the next line *LineNum = *LineNum +1 before it crashes displaying the send error message dialog. I'm running 2002 SP3. Regards ojv Sub AddEvents() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Dim body As String Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("ThisWorkbook") Set CodeMod = VBComp.CodeModule body = "Dim i as Integer" & vbCrLf & "i=0" With CodeMod * * LineNum = .CreateEventProc("Open", "Workbook") * * LineNum = LineNum + 1 * * .InsertLines LineNum, body End With Set VBComp = VBProj.VBComponents(Worksheets(1).codeName) Set CodeMod = VBComp.CodeModule With CodeMod * * LineNum = .CreateEventProc("Change", "Worksheet") * * LineNum = LineNum + 1 * * .InsertLines LineNum, body End With End Sub Hi ojv. I believe you had to insert one instructions in one line. Try: Sub AddEvents() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents("ThisWorkbook") Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CreateEventProc("Open", "Workbook") LineNum = LineNum + 1 .InsertLines LineNum, "Dim i as Integer" LineNum = LineNum + 1 .InsertLines LineNum, "i=0" End With End Sub Regards Eliano |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
Any resolution to this issue? I am having the same problem. thanks "ojv" wrote: I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String For Each vbc In ActiveWorkbook.VBProject.VBComponents If vbc.name = Worksheets(1).codeName Then pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") body = wsEvent vbc.CodeModule.InsertLines pos, body Exit For End If Next End Sub What am i doing wrong? Any help appreciated ojv |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Worksheet change procedure
On 9 Lug, 19:33, jk wrote:
Any resolution to this issue? I am having the same problem. *thanks "ojv" wrote: I get a runtime error in Excel using the following method: Sub AddEvents() Dim vbc As VBComponent, pos As Long, body as String * * For Each vbc In ActiveWorkbook.VBProject.VBComponents * * * * If vbc.name = Worksheets(1).codeName Then * * * * * * pos = vbc.CodeModule.CreateEventProc("Change", "Worksheet") * * * * * * body = wsEvent * * * * * * vbc.CodeModule.InsertLines pos, body * * * * * * Exit For * * * * End If * * Next End Sub What am i doing wrong? Any help appreciated ojv- Nascondi testo citato - Mostra testo citato - Hi jk. I believe that my post of 5 July is valid, however for further informations, see: http://www.cpearson.com/excel/vbe.aspx Regards Eliano |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Worksheet change procedure | Excel Programming | |||
Run procedure from worksheet change event | Excel Programming | |||
Validation Procedure with a worksheet change event | Excel Worksheet Functions | |||
Excel locks with Worksheet Change procedure | Excel Programming | |||
Worksheet change sub procedure | Excel Programming |