Change worksheet formulas from button on chart sheet
 

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

Change worksheet formulas from button on chart sheet
 

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" wrote in message
...
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