Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change the pivot chart automaticaly as values in the worksheet change | Excel Worksheet Functions | |||
Calling a macro on a change of Field Button in Pivot Chart | Excel Programming | |||
how to change range for dynamic chart in excel 2000 with button? | Charts and Charting in Excel | |||
Change buttoncolor on button on a sheet from VBA | Excel Programming | |||
Change Chart Typewith button | Excel Programming |