ExcelBanter - Insert Worksheet change procedure

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Insert Worksheet change procedure (https://www.excelbanter.com/excel-programming/429409-insert-worksheet-change-procedure.html)

ojv[_2_]

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

eliano[_2_]

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

eliano[_2_]

Insert Worksheet change procedure

ojv[_2_]

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

ojv[_2_]

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

ojv[_2_]

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

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

Insert Worksheet change procedure

ojv[_2_]

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

ojv[_2_]

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

eliano[_2_]

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

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

eliano[_2_]

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

All times are GMT +1. The time now is 01:53 AM.