Target Range Activation
in [Programming]
|
Prev: Marco to protect / unprotect worksheet
Next: ActiveWorkbook.SaveAs Function - Can I specify the user account to save the file with
From: Philosophaie on 24 Jan 2010 17:43 "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
From: Philosophaie on 24 Jan 2010 17:54 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.
From: Chip Pearson on 24 Jan 2010 18:39 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(a)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.
From: Philosophaie on 24 Jan 2010 20:59 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.
From: Dave Peterson on 24 Jan 2010 23:07
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 |