Mark Dvorkin
6/1/2016 8:13:00 AM
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