Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 53
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 79
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 79
Default 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   Report Post  
Posted to microsoft.public.excel.programming
jk jk is offline
external usenet poster
 
Posts: 109
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 79
Default 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
Search this Thread:

Advanced Search
Display Modes

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
Insert Worksheet change procedure r Excel Programming 0 June 4th 09 11:08 PM
Run procedure from worksheet change event ranswert Excel Programming 1 January 17th 08 07:49 PM
Validation Procedure with a worksheet change event Bhupinder Rayat Excel Worksheet Functions 2 October 3rd 07 05:18 PM
Excel locks with Worksheet Change procedure Casey[_62_] Excel Programming 0 March 22nd 06 03:14 PM
Worksheet change sub procedure Grant Excel Programming 5 September 24th 04 01:44 AM


All times are GMT +1. The time now is 05:55 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"