Code Interferes with other Worksheet Change Events.

[Arnold]

Hi All,

I obtained code from a thread "Automatically fill down formulas when
adding a record" and modified it because I had more formulas in more
columns to carry down to new records. It works but it also interferes
with other Worksheet_Change events.

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
'Here are the formulas to carry down to new records.
.Offset(0, 3).FormulaR1C3 = "=RC[-1]=$B2"
.Offset(0, 10).formulaR1C10 = "=RC[-1]=$J2"
End If
End With
CleanUp:
Application.EnableEvents = True

For instance, in the last column IV, there is a named range called
AbbNames that is defined as =OFFSET(Abbreviated,0,246). AbbNames does
not now update with new records. However, Abbreviated, which is
another named range, does update.

Basically, I need a way to add new records both with and without the
use of the data form, which extends formulas.

Thanks,
Arnold

 

[Dave Peterson]

First, I don't think your event actually does work.

There's not .formulaR1C3 or .formulaR1C10 property. There is a .formulaR1C1
property that means that the formula you're gonna use is written in R1C1
reference style--not A1 reference style.

And you're mixing those two styles in those lines.

Maybe something like this instead:
.Offset(0, 3).FormulaR1C1 = "=RC[-1]=R2C2"
.Offset(0, 10).formulaR1C1 = "=RC[-1]=R2C10"

And if you use Data|Form, you're limited to 32 columns. I don't see how
anything in column IV would be included in that Data|Form dialog.

And I'm not sure what you mean by interfering, either. I don't see anything in
your code that touches column IV.

Hi All,

I obtained code from a thread "Automatically fill down formulas when
adding a record" and modified it because I had more formulas in more
columns to carry down to new records. It works but it also interferes
with other Worksheet_Change events.

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
'Here are the formulas to carry down to new records.
.Offset(0, 3).FormulaR1C3 = "=RC[-1]=$B2"
.Offset(0, 10).formulaR1C10 = "=RC[-1]=$J2"
End If
End With
CleanUp:
Application.EnableEvents = True

For instance, in the last column IV, there is a named range called
AbbNames that is defined as =OFFSET(Abbreviated,0,246). AbbNames does
not now update with new records. However, Abbreviated, which is
another named range, does update.

Basically, I need a way to add new records both with and without the
use of the data form, which extends formulas.

Thanks,
Arnold