Change worksheet formulas from button on chart sheet

[David]

Greetings,
This one has really got me scratching my head
The macros below change the formulas in column ranges "required" and
"completed". They work fine when run from a forms menu button on any
worksheet, but when I try to run them from a similar button on a chart sheet
I get the following extraordinary results for the 4 formula variants:
=SUMPRODUCT(--(LEFT(R[-6]C[212]:R[-6]C[230])<>"X"))
=SUMPRODUCT(--(R[-6]C[210]:R[-6]C[228]="C"))
=SUMPRODUCT(--(R[-6]C[210]:R[-6]C[228]="C"),UserWeighting)
=SUMPRODUCT(--(LEFT(R[-6]C[212]:R[-6]C[230])<>"X"),UserWeighting)

Sub ApplyWeights()
With Sheets("Matrix")
.Range("Required").FormulaR1C1 = _
"=SUMPRODUCT(--(LEFT(RC[-19]:RC[-1])<>""X""),UserWeighting)"
.Range("Completed").FormulaR1C1 = _
"=SUMPRODUCT(--(RC[-20]:RC[-2]=""C""),UserWeighting)"
End With
End Sub

Sub RemoveWeights()
With Sheets("Matrix")
.Range("Required").FormulaR1C1 = _
"=SUMPRODUCT(--(LEFT(RC[-19]:RC[-1])<>""X""))"
.Range("Completed").FormulaR1C1 = _
"=SUMPRODUCT(--(RC[-20]:RC[-2]=""C""))"
End With
End Sub

Any advice will be much appreciated
TIA
--
David

[Tom Ogilvy]

the quick fix would be

Dim sh as Object
set sh = Activesheet

Application.ScreenUpdating = False
Wth Sheets("Matrix")
.Activate


sh.Activate
End With
Application.ScreenUpdating = True

--
Regards,
Tom Ogilvy



"David" <David@discussions.microsoft.com> wrote in message
news:D71093D0-D74A-4539-90EF-B4DC5AE57722@microsoft.com...
> Greetings,
> This one has really got me scratching my head
> The macros below change the formulas in column ranges "required" and
> "completed". They work fine when run from a forms menu button on any
> worksheet, but when I try to run them from a similar button on a chart
sheet
> I get the following extraordinary results for the 4 formula variants:
> =SUMPRODUCT(--(LEFT(R[-6]C[212]:R[-6]C[230])<>"X"))
> =SUMPRODUCT(--(R[-6]C[210]:R[-6]C[228]="C"))
> =SUMPRODUCT(--(R[-6]C[210]:R[-6]C[228]="C"),UserWeighting)
> =SUMPRODUCT(--(LEFT(R[-6]C[212]:R[-6]C[230])<>"X"),UserWeighting)
>
> Sub ApplyWeights()
> With Sheets("Matrix")
> .Range("Required").FormulaR1C1 = _
> "=SUMPRODUCT(--(LEFT(RC[-19]:RC[-1])<>""X""),UserWeighting)"
> .Range("Completed").FormulaR1C1 = _
> "=SUMPRODUCT(--(RC[-20]:RC[-2]=""C""),UserWeighting)"
> End With
> End Sub
>
> Sub RemoveWeights()
> With Sheets("Matrix")
> .Range("Required").FormulaR1C1 = _
> "=SUMPRODUCT(--(LEFT(RC[-19]:RC[-1])<>""X""))"
> .Range("Completed").FormulaR1C1 = _
> "=SUMPRODUCT(--(RC[-20]:RC[-2]=""C""))"
> End With
> End Sub
>
> Any advice will be much appreciated
> TIA
> --
> David