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