Programmatically Adding a Worksheet_Change Event Procedure to a Pr

Prev: Change to different active printers on different PC's
Next: Inserting the values of a formula using vb

From: dougp on 22 Apr 2010 10:13

---

I am using Excel 2003 and have added a script to add a Worksheet_Change Event
to an Added Worksheet. I receive the following error: Error -2147417848
(80010108): The object invoked has disconnected from its clients.



I have referenced the Microsoft Visual Basic for Applications Extensibility
5.3. I understand this script would run in the background and naming
VBProject as an Object and using the CreateObject as Microsoft describes in
Article ID: 319832 - Last Review: February 1, 2007 - Revision: 5.3

INFO: Error or Unexpected Behavior with Office Automation When You Use Early
Binding in Visual Basic
does not work.



Here is a portion of the script and where the ERROR occurs:



Dim wsn As String

Dim VBProj As VBIDE.VBProject

Dim VBComp As VBIDE.VBComponent

Dim CodeMod As VBIDE.CodeModule

Dim LineNum As Long

Const DQUOTE = """"



wsn = ActiveSheet.Name



Application.EnableEvents = False

Set VBProj = ActiveWorkbook.VBProject

Set VBComp = VBProj.VBComponents(Worksheets(wsn).CodeName).CodeModule

Set CodeMod = VBComp.Document



With CodeMod

LineNum = .CountOfLines + 1

.InsertLines LineNum, "Option Explicit" & vbCrLf

LineNum = LineNum + 1

.InsertLines LineNum, vbCrLf

LineNum = LineNum + 1

.InsertLines LineNum, _

"Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf

LineNum = LineNum + 1 (Here is where I receive the ERROR)

.InsertLines LineNum, "Dim rngDV As Range" & vbCrLf

LineNum = LineNum + 1

.InsertLines LineNum, "Dim oldVal As String" & vbCrLf

From: JLatham on 22 Apr 2010 10:41

---

Doug,
A much better way to do this, especially with worksheet or workbook events
is described (with code examples) here:
http://www.cpearson.com/Excel/VBE.aspx
look down to where he codes up a Workbook_Open() event
Creating An Event Procedure

The code adapts easily to worksheets also. I know, because I've used it in
just that manner.

"dougp" wrote:

> I am using Excel 2003 and have added a script to add a Worksheet_Change Event
> to an Added Worksheet. I receive the following error: Error -2147417848
> (80010108): The object invoked has disconnected from its clients.
>
>
>
> I have referenced the Microsoft Visual Basic for Applications Extensibility
> 5.3. I understand this script would run in the background and naming
> VBProject as an Object and using the CreateObject as Microsoft describes in
> Article ID: 319832 - Last Review: February 1, 2007 - Revision: 5.3
>
> INFO: Error or Unexpected Behavior with Office Automation When You Use Early
> Binding in Visual Basic
> does not work.
>
>
>
> Here is a portion of the script and where the ERROR occurs:
>
>
>
> Dim wsn As String
>
> Dim VBProj As VBIDE.VBProject
>
> Dim VBComp As VBIDE.VBComponent
>
> Dim CodeMod As VBIDE.CodeModule
>
> Dim LineNum As Long
>
> Const DQUOTE = """"
>
>
>
> wsn = ActiveSheet.Name
>
>
>
> Application.EnableEvents = False
>
> Set VBProj = ActiveWorkbook.VBProject
>
> Set VBComp = VBProj.VBComponents(Worksheets(wsn).CodeName).CodeModule
>
> Set CodeMod = VBComp.Document
>
>
>
> With CodeMod
>
> LineNum = .CountOfLines + 1
>
> .InsertLines LineNum, "Option Explicit" & vbCrLf
>
> LineNum = LineNum + 1
>
> .InsertLines LineNum, vbCrLf
>
> LineNum = LineNum + 1
>
> .InsertLines LineNum, _
>
> "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf
>
> LineNum = LineNum + 1 (Here is where I receive the ERROR)
>
> .InsertLines LineNum, "Dim rngDV As Range" & vbCrLf
>
> LineNum = LineNum + 1
>
> .InsertLines LineNum, "Dim oldVal As String" & vbCrLf
>
>
>
>

From: Dave Peterson on 22 Apr 2010 10:53

---

I wouldn't approach it this way -- especially if you're supporting a group of
users.

For this kind of code to work, each user will have to have their security
settings set to allow access to the VBE. And your code can't control that
setting.

And if the workbook's project is protected, then you're in trouble with that
setting, too.

Instead, if this is just a single workbook that needs this, I'd create a new
sheet with all the event code that I wanted already in it -- in fact, I'd format
it, add controls, pictures, headers, page setup, ... all the stuff I know has to
be done anyway.

Then instead of adding a new sheet, I'd just copy this template sheet and use
that copy.

======
If I had lots of workbooks that needed this same worksheet, then I'd create a
new workbook with that single sheet (and all the stuff I wanted!) and then just
add that sheet to the existing workbook.



dougp wrote:
>
> I am using Excel 2003 and have added a script to add a Worksheet_Change Event
> to an Added Worksheet. I receive the following error: Error -2147417848
> (80010108): The object invoked has disconnected from its clients.
>
>
>
> I have referenced the Microsoft Visual Basic for Applications Extensibility
> 5.3. I understand this script would run in the background and naming
> VBProject as an Object and using the CreateObject as Microsoft describes in
> Article ID: 319832 - Last Review: February 1, 2007 - Revision: 5.3
>
> INFO: Error or Unexpected Behavior with Office Automation When You Use Early
> Binding in Visual Basic
> does not work.
>
>
>
> Here is a portion of the script and where the ERROR occurs:
>
>
>
> Dim wsn As String
>
> Dim VBProj As VBIDE.VBProject
>
> Dim VBComp As VBIDE.VBComponent
>
> Dim CodeMod As VBIDE.CodeModule
>
> Dim LineNum As Long
>
> Const DQUOTE = """"
>
>
>
> wsn = ActiveSheet.Name
>
>
>
> Application.EnableEvents = False
>
> Set VBProj = ActiveWorkbook.VBProject
>
> Set VBComp = VBProj.VBComponents(Worksheets(wsn).CodeName).CodeModule
>
> Set CodeMod = VBComp.Document
>
>
>
> With CodeMod
>
> LineNum = .CountOfLines + 1
>
> .InsertLines LineNum, "Option Explicit" & vbCrLf
>
> LineNum = LineNum + 1
>
> .InsertLines LineNum, vbCrLf
>
> LineNum = LineNum + 1
>
> .InsertLines LineNum, _
>
> "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf
>
> LineNum = LineNum + 1 (Here is where I receive the ERROR)
>
> .InsertLines LineNum, "Dim rngDV As Range" & vbCrLf
>
> LineNum = LineNum + 1
>
> .InsertLines LineNum, "Dim oldVal As String" & vbCrLf
>
>

--

Dave Peterson

From: Peter T on 22 Apr 2010 11:20

---

"Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message

Just to add

> Then instead of adding a new sheet, I'd just copy this template sheet and
> use
> that copy.

Could save the file with the single worksheet code as a Template, then
simply

Sheets.Add Type:="C:\<path>\myWorksheetCode.xlt"

Regards,
Peter T

From: Rick Rothstein on 22 Apr 2010 13:26

---

Another possibility, depending on what the code is supposed to do, might be
to use the Workbook's SheetChange event which allows you to check which
sheet the change took place on along with the target range which changed.

--
Rick (MVP - Excel)



"Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message
news:4BD062FD.8D6140AE(a)verizonXSPAM.net...
> I wouldn't approach it this way -- especially if you're supporting a group
> of
> users.
>
> For this kind of code to work, each user will have to have their security
> settings set to allow access to the VBE. And your code can't control that
> setting.
>
> And if the workbook's project is protected, then you're in trouble with
> that
> setting, too.
>
> Instead, if this is just a single workbook that needs this, I'd create a
> new
> sheet with all the event code that I wanted already in it -- in fact, I'd
> format
> it, add controls, pictures, headers, page setup, ... all the stuff I know
> has to
> be done anyway.
>
> Then instead of adding a new sheet, I'd just copy this template sheet and
> use
> that copy.
>
> ======
> If I had lots of workbooks that needed this same worksheet, then I'd
> create a
> new workbook with that single sheet (and all the stuff I wanted!) and then
> just
> add that sheet to the existing workbook.
>
>
>
> dougp wrote:
>>
>> I am using Excel 2003 and have added a script to add a Worksheet_Change
>> Event
>> to an Added Worksheet. I receive the following error: Error -2147417848
>> (80010108): The object invoked has disconnected from its clients.
>>
>>
>>
>> I have referenced the Microsoft Visual Basic for Applications
>> Extensibility
>> 5.3. I understand this script would run in the background and naming
>> VBProject as an Object and using the CreateObject as Microsoft describes
>> in
>> Article ID: 319832 - Last Review: February 1, 2007 - Revision: 5.3
>>
>> INFO: Error or Unexpected Behavior with Office Automation When You Use
>> Early
>> Binding in Visual Basic
>> does not work.
>>
>>
>>
>> Here is a portion of the script and where the ERROR occurs:
>>
>>
>>
>> Dim wsn As String
>>
>> Dim VBProj As VBIDE.VBProject
>>
>> Dim VBComp As VBIDE.VBComponent
>>
>> Dim CodeMod As VBIDE.CodeModule
>>
>> Dim LineNum As Long
>>
>> Const DQUOTE = """"
>>
>>
>>
>> wsn = ActiveSheet.Name
>>
>>
>>
>> Application.EnableEvents = False
>>
>> Set VBProj = ActiveWorkbook.VBProject
>>
>> Set VBComp =
>> VBProj.VBComponents(Worksheets(wsn).CodeName).CodeModule
>>
>> Set CodeMod = VBComp.Document
>>
>>
>>
>> With CodeMod
>>
>> LineNum = .CountOfLines + 1
>>
>> .InsertLines LineNum, "Option Explicit" & vbCrLf
>>
>> LineNum = LineNum + 1
>>
>> .InsertLines LineNum, vbCrLf
>>
>> LineNum = LineNum + 1
>>
>> .InsertLines LineNum, _
>>
>> "Private Sub Worksheet_Change(ByVal Target As Range)" &
>> vbCrLf
>>
>> LineNum = LineNum + 1 (Here is where I receive the ERROR)
>>
>> .InsertLines LineNum, "Dim rngDV As Range" & vbCrLf
>>
>> LineNum = LineNum + 1
>>
>> .InsertLines LineNum, "Dim oldVal As String" & vbCrLf
>>
>>
>
> --
>
> Dave Peterson

 |  Next  |  Last
Pages: 1 2
Prev: Change to different active printers on different PC's
Next: Inserting the values of a formula using vb