Trying to get Wooksheet_Change to work:

Public rng As Range
Private Sub Workbook_Open()
    Dim range1 As Range
    Set range1 = Sheet4.Range("D4:F500")
    Set rng = range1

'I need a way to access the subroutine Worksheet_Change 
'to fire the object Target range. I tried: 
'Worksheet_Change(rng)        that didn't work.

End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    
End Sub

Detail on what you are trying to do.??

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Philosophaie" <Philosophaie@discussions.microsoft.com> wrote in message 
news:29A5613F-6BA9-4256-AD55-099235798EC1@microsoft.com...
> Trying to get Wooksheet_Change to work:
>
> Public rng As Range
> Private Sub Workbook_Open()
>    Dim range1 As Range
>    Set range1 = Sheet4.Range("D4:F500")
>    Set rng = range1
>
> 'I need a way to access the subroutine Worksheet_Change
> 'to fire the object Target range. I tried:
> 'Worksheet_Change(rng)        that didn't work.
>
> End Sub
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> End Sub 

This code works

Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D4:F500")) Is Nothing Then
    MsgBox "Cell in D4:F500 has be changed"
  End If
End Sub

Remember it must be added to the Sheet model not the Genaeal module
One way is to right click the sheet's tab and select View Code

best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Philosophaie" <Philosophaie@discussions.microsoft.com> wrote in message 
news:29A5613F-6BA9-4256-AD55-099235798EC1@microsoft.com...
> Trying to get Wooksheet_Change to work:
>
> Public rng As Range
> Private Sub Workbook_Open()
>    Dim range1 As Range
>    Set range1 = Sheet4.Range("D4:F500")
>    Set rng = range1
>
> 'I need a way to access the subroutine Worksheet_Change
> 'to fire the object Target range. I tried:
> 'Worksheet_Change(rng)        that didn't work.
>
> End Sub
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> End Sub 

The Worksheet_Change procedure MUST be located in the code module for
the worksheet whose changes you want to trap.  Each worksheet whose
changes need to be trapped will have its own Change event procedure in
its own code module. If you have Worksheet_Change in the ThisWorkbook
module, VBA does not recognize it as an event procedure and the
procedure will not be called automatically. VBA sees is as just
another ordinary procedure.

If you want to trap changes for all sheets in the ThisWorkbook module,
use the workbook's SheetChange event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
' your code here
End Sub

In this procedure, Sh references the worksheet on which the change
occurred, and Target references the cells on Sh that were changed.
This will trap changes on any sheet in the workbook (but not in other
workbooks).

For much more information about event procedures, see
http://www.cpearson.com/excel/Events.aspx .

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Sun, 24 Jan 2010 13:13:01 -0800, Philosophaie
<Philosophaie@discussions.microsoft.com> wrote:

>Trying to get Wooksheet_Change to work:
>
>Public rng As Range
>Private Sub Workbook_Open()
>    Dim range1 As Range
>    Set range1 = Sheet4.Range("D4:F500")
>    Set rng = range1
>
>'I need a way to access the subroutine Worksheet_Change 
>'to fire the object Target range. I tried: 
>'Worksheet_Change(rng)        that didn't work.
>
>End Sub
>Private Sub Worksheet_Change(ByVal Target As Range)
>    
>End Sub

I am trying to set the Target from the Workbook_Open subroutine so the 
Worksheet_Change will fire when the cell in that range has been changed.  I 
need a way to get the object in Workbook_Open subroutine to set the object in 
Worksheet_Change to initiallize Target range. 

"Bernard Liengme" wrote:

> This code works
> 
> Sub Worksheet_Change(ByVal Target As Range)
>   If Not Intersect(Target, Range("D4:F500")) Is Nothing Then
>     MsgBox "Cell in D4:F500 has be changed"
>   End If
> End Sub
> 
> Remember it must be added to the Sheet model not the Genaeal module
> One way is to right click the sheet's tab and select View Code
> 
> best wishes
> -- 
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme

 I already have this in my code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim trgt As Range
    Dim intersect As Range
    Set intersect = Application.intersect(trgt, Target)
    If Not intersect Is Nothing Then
        With Sheets("Sheet4")
        For n = 2 To 500
            Cells(n, 5) = .cell((n - 1), 5) + .cell(n, 6) - .cell(n, 4)
        Next n
        End With
     End If
End Sub

I would like to set the Target from the Workbook_Open subroutine so the 
Worksheet_Change will fire when the cell in that range has been changed. I 
need a way to get the object in Workbook_Open subroutine to set the object in 
Worksheet_Change to initiallize Target range. 

This has been answered several times. Do NOT (!) put your
Worksheet_Change code in the ThisWorkbook code module. It MUST reside
in the code module of the worksheet whose changes you want to trap.
You don't call Worksheet_Change directly; Excel calls it automatically
when a cell value is changed. It calls the Change event code that is
in its own code module. If you put Worksheet_Change in the
ThisWorkbook module, Excel doesn't recognize it as an event procedure
and will not execute it. 

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sun, 24 Jan 2010 14:54:01 -0800, Philosophaie
<Philosophaie@discussions.microsoft.com> wrote:

>I would like to set the Target from the Workbook_Open subroutine so the 
>Worksheet_Change will fire when the cell in that range has been changed. I 
>need a way to get the object in Workbook_Open subroutine to set the object in 
>Worksheet_Change to initiallize Target range. 

You have all my code.  Why can't I figure out why the Worksheet_Change is not 
firing when a cell in the range is manipulated?  I do save and exit the 
workbook before I run the updated program and I am using a xls file in Excel 
2007.

In this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim trgt As Range
    Dim intersect As Range
    Set intersect = Application.intersect(trgt, Target)
    If Not intersect Is Nothing Then
        With Sheets("Sheet4")
        For n = 2 To 500
            Cells(n, 5) = .cell((n - 1), 5) + .cell(n, 6) - .cell(n, 4)
        Next n
        End With
     End If
End Sub

trgt is never set to anything.

Are you trying to pass that range from the workbook_open event to that sheet's
_change event?

If yes, you could create a new module (Insert|Module) and put this into the
module:

Option Explicit
Public trgt as range

Then in the ThisWorkbook module, change your workbook_open procedure to use that
public variable:

Private Sub Workbook_Open()
    Set trgt = Sheet4.Range("D4:F500")

Since trgt is declared in a General Module, every procedure in every module can
see it.

Then make sure you delete the declaration in Sheet4's worksheet module.  So the
code becomes:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myIntersect As Range 'don't use a variable named Intersect!
    Dim N as long
    Set myintersect = Application.intersect(trgt, Target)
    If Not myintersect Is Nothing Then
        With Me 'the sheet owning the code.
          'stop this event from firing itself
          application.enableevents = false
          For n = 2 To 500
           'added a leading dot to the first cells() reference      
            .Cells(n, 5) = .cell((n - 1), 5) + .cell(n, 6) - .cell(n, 4)
          Next n
          application.enableevents = true
        End With
     End If
End Sub

==========
If you wanted this event to fire each time you opened the workbook, then you
could just change a value to itself in the range you want to inspect.

In the ThisWorkbook module:

Private Sub Workbook_Open()
    with sheet4
       Set trgt = .Range("D4:F500")
       with .range("d4")
           .value = .value
       end with
    end with
End Sub

================
You could call the worksheet_Change event in sheet4 IF you make a change to the
procedure statement:

Private Sub Worksheet_Change(ByVal Target As Range)
becomes
Sub Worksheet_Change(ByVal Target As Range)

And the code would look like:

Private Sub Workbook_Open()
    with sheet4
       Set trgt = .Range("D4:F500")
       Call .Worksheet_Change(Target:=.Range("d4"))
    end with
End Sub


    


Philosophaie wrote:
> 
> "Bernard Liengme" wrote:
> 
> > This code works
> >
> > Sub Worksheet_Change(ByVal Target As Range)
> >   If Not Intersect(Target, Range("D4:F500")) Is Nothing Then
> >     MsgBox "Cell in D4:F500 has be changed"
> >   End If
> > End Sub
> >
> > Remember it must be added to the Sheet model not the Genaeal module
> > One way is to right click the sheet's tab and select View Code
> >
> > best wishes
> > --
> > Bernard Liengme
> > Microsoft Excel MVP
> > http://people.stfx.ca/bliengme
> 
>  I already have this in my code:
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
>     Dim trgt As Range
>     Dim intersect As Range
>     Set intersect = Application.intersect(trgt, Target)
>     If Not intersect Is Nothing Then
>         With Sheets("Sheet4")
>         For n = 2 To 500
>             Cells(n, 5) = .cell((n - 1), 5) + .cell(n, 6) - .cell(n, 4)
>         Next n
>         End With
>      End If
> End Sub

-- 

Dave Peterson

For the nth time, I will repeat that the Worksheet_Change event
procedure must NOT (!!!!!!!) be in the ThisWorkbook code module. If it
is, it will not be recognized by VBA and will not run when a cell
value is changed. The Worksheet_Change event MUST (!!!!!!) go in the
sheet module of the worksheet whose changes are to trapped.  In Excel,
right-click on the tab of the sheet whose changes you want to trap,
and choose "View Code". Put your that code module. Again, do NOT (!)
put the Worksheet_Change event code in the ThisWorkbook module. 

Maybe you don't understand what events are. See
http://www.cpearson.com/excel/Events.aspx for much more detail about
events, how they work, and how to code them. 

Finally, again, do NOT put the Worksheet_Change event in the
ThisWorkbook module. This will NOT work.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sun, 24 Jan 2010 17:59:01 -0800, Philosophaie
<Philosophaie@discussions.microsoft.com> wrote:

>You have all my code.  Why can't I figure out why the Worksheet_Change is not 
>firing when a cell in the range is manipulated?  I do save and exit the 
>workbook before I run the updated program and I am using a xls file in Excel 
>2007.

Chip
Remember the Paul Newman movie "Cool hand Luke" and the guard said to Luke, 
"What we have here is a failure to cummunikate (sp)
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Chip Pearson" <chip@cpearson.com> wrote in message 
news:5u6ul512jvjvucp2pe924bulrfdduecuf5@4ax.com...
> For the nth time, I will repeat that the Worksheet_Change event
> procedure must NOT (!!!!!!!) be in the ThisWorkbook code module. If it
> is, it will not be recognized by VBA and will not run when a cell
> value is changed. The Worksheet_Change event MUST (!!!!!!) go in the
> sheet module of the worksheet whose changes are to trapped.  In Excel,
> right-click on the tab of the sheet whose changes you want to trap,
> and choose "View Code". Put your that code module. Again, do NOT (!)
> put the Worksheet_Change event code in the ThisWorkbook module.
>
> Maybe you don't understand what events are. See
> http://www.cpearson.com/excel/Events.aspx for much more detail about
> events, how they work, and how to code them.
>
> Finally, again, do NOT put the Worksheet_Change event in the
> ThisWorkbook module. This will NOT work.
>
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
> [email on web site]
>
>
>
> On Sun, 24 Jan 2010 17:59:01 -0800, Philosophaie
> <Philosophaie@discussions.microsoft.com> wrote:
>
>>You have all my code.  Why can't I figure out why the Worksheet_Change is 
>>not
>>firing when a cell in the range is manipulated?  I do save and exit the
>>workbook before I run the updated program and I am using a xls file in 
>>Excel
>>2007.