Prev: Change to different active printers on different PC's
Next: Inserting the values of a formula using vb
From: Dave Peterson on 22 Apr 2010 13:36 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" <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 -- Dave Peterson
First
|
Prev
|
Pages: 1 2 Prev: Change to different active printers on different PC's Next: Inserting the values of a formula using vb |