Programmatically Adding a Worksheet_Change Event Procedure to a Pr
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).Code Module
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
|