Target Range Activation

[Philosophaie]

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

 

[Don Guillett]

Detail on what you are trying to do.??

 

[Bernard Liengme]

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

 

[Chip Pearson]

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]

 

[Philosophaie]

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.

 

[Philosophaie]

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

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

 

[Philosophaie]

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.

 

[Chip Pearson]

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]

 

[Philosophaie]

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.

 

[Dave Peterson]

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

 

[Chip Pearson]

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]

 

[Don Guillett]

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
(e-mail address removed)

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]

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.