Could someone please explain how the argument Target passed-in to
the event-driven sub below.

Specifically:
1. Do I need to set the Target range within the sub body
or XL does it for me with the declaration
Target As Excel.Range
setting it to A1:maxColmaxRow? My actual range is B2:X37

2. Why the sub must be in worksheet code module (right-click
the worksheet tab and choose View Code) and my prevDay(Ref) function
and other VBA code must be in the Module1 (insert module)?

When I make a copy of the sheet within the workbook (Jul05, Aug05)
each of which has one sheet per day, will the code be repeated that
many times?

thanks in advance for your help,
/mark

'A2 of an active sheet gets [via function =prevDay(A1)] the value
'from A1 of the previous sheet. If needed this value can be overwritten
'by user (i.e. the value and the formula will be overwritten with a new value).
'If the user deletes the value in A2 the code below puts the formula
'back into the cell. (the sub was donated to me by J.E. McGimpsey)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A2" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "=prevDay(A1)"
Application.EnableEvents = True
End If
End If
End With
End Sub

Everything is an object : this mean the code is associated to the object
it is written in. A module is generic and may be used by other objects.

the "Sub Worksheet_Change" is a reserved name and mean you want the
macro to be auto-called when a special event (here any change in the
entry of a cell: only the entry, if an unchanged formula gives a new
result, this will not be a cell change).

So, since this macro must be lauched by the worksheet (as an object)
it must be associated to this worksheet.

So the worksheet launch the "Worksheet_Change" Zub (inside itself)
when a change occurs. But it will not launch anything else neither
elsewhere.
Each sheet may have its own event management

Then: now it's like any other sub: the name is reserved, and the sub is
called by an event, but the syntax stays as usual: so this mean the
caller (here the worksheet) will gives the input. Target is an input.
You can know which cells are the changed ones.

"Mark Dvorkin" wrote in message
...
Could someone please explain how the argument Target passed-in to
the event-driven sub below.

Specifically:
1. Do I need to set the Target range within the sub body
or XL does it for me with the declaration
Target As Excel.Range
setting it to A1:maxColmaxRow? My actual range is B2:X37

No, the event is trapped within Excel when a cell or cells are change. The
range of cells veing changed is passed to the event procedure as Target.

You don't have to call it Target, it can be any n ame, it is just that when
the template procedure is created it is assigned the name Target.

2. Why the sub must be in worksheet code module (right-click
the worksheet tab and choose View Code) and my prevDay(Ref) function
and other VBA code must be in the Module1 (insert module)?

Yes it must, as it is associated with an event taking place on that
worksheet. If it were in a standard code module, Excel would not know which
worksheet it applied to.

When I make a copy of the sheet within the workbook (Jul05, Aug05)
each of which has one sheet per day, will the code be repeated that
many times?

Yes it will.

Hello Mark,

"Mark Dvorkin" wrote 1. Do I need to set the Target range within the sub
body
or XL does it for me with the declaration
Target As Excel.Range
setting it to A1:maxColmaxRow? My actual range is B2:X37

1. Target is declared as an argument, so don't need to declare it again
2. Target is the range on which the current event has been triggered (the
range affected by change, selection change, etc.), so you shouldn't be
changing it, but using as is.
3. By setting Target to A1:maxColmaxRow, it is no longer Target, but another
range so what is the point of changing it.
4. Usually, if you want to check wheather the Target range falls in or
intersects with a specific range you would use either:

If Not Intersect(Target, Range("A1:A" & maxColmaxRow)) Is Nothing Then
'your code here
End If

or the one that you actually use

2. Why the sub must be in worksheet code module (right-click
the worksheet tab and choose View Code) and my prevDay(Ref) function
and other VBA code must be in the Module1 (insert module)?

1. The event procedure must be in the module whose event is being captured,
I guess, for obvious reasons.
2. Your functions don't have to be in a standard module if you want to use
them from a specific class module (e.g. Worksheet) - you can put them into
the same module as the event procedure.
3. If you want the functions to be directly available for other modules or
as a spreadsheet function then you are better off putting them into standard
modules e.g. Module1.

When I make a copy of the sheet within the workbook (Jul05, Aug05)
each of which has one sheet per day, will the code be repeated that
many times?

Yes,the code will be multiplied if you copy a sheet which contains code.
I guess you should consider the ThisWorkbook event SheetChange if you want
to capture the event on multiple sheets

Privet,
KL

thanks to all of you, for your clarifications, for starting points to
dig further and especially for your patience
/mark

Mark Dvorkin wrote:

Could someone please explain how the argument Target passed-in to the
event-driven sub below.

Specifically:
1. Do I need to set the Target range within the sub body
or XL does it for me with the declaration
Target As Excel.Range
setting it to A1:maxColmaxRow? My actual range is B2:X37

2. Why the sub must be in worksheet code module (right-click
the worksheet tab and choose View Code) and my prevDay(Ref) function
and other VBA code must be in the Module1 (insert module)?

When I make a copy of the sheet within the workbook (Jul05, Aug05)
each of which has one sheet per day, will the code be repeated
that many times?

thanks in advance for your help,
/mark

'A2 of an active sheet gets [via function =prevDay(A1)] the value
'from A1 of the previous sheet. If needed this value can be overwritten
'by user (i.e. the value and the formula will be overwritten with a
new value). 'If the user deletes the value in A2 the code below puts
the formula 'back into the cell. (the sub was donated to me by J.E.
McGimpsey)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A2" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "=prevDay(A1)"
Application.EnableEvents = True
End If
End If
End With
End Sub