Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically Adding a Worksheet_Change Event Procedure to a Pr
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically Adding a Worksheet_Change Event Procedure to a Pr
Doug,
A much better way to do this, especially with worksheet or workbook events is described (with code examples) he 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).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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically Adding a Worksheet_Change Event Procedure to a Pr
"Dave Peterson" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically Adding a Worksheet_Change Event Procedure to a Pr
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically Adding a Worksheet_Change Event Procedure to a Pr
And maybe even add a hidden name that the OP can check to see if this is one of
the worksheets that should be processed. Rick Rothstein wrote: 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" wrote in message ... 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmatically adding checkbox and attaching event handlers | Excel Programming | |||
adding usr defined event procedure of textbox in usrform ?? | Excel Programming | |||
Worksheet_Change procedure | Excel Programming | |||
Worksheet_Change procedure | Excel Programming | |||
Worksheet_Change procedure | Excel Programming |