D
dougp
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
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