Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update worksheet formula
Is it possible to only update the result of a formulas on a woorksheet
when a particular condition is met. I would like to have a button that when presses updates the worksheet. The reason is I have a lot of formulas referencing other worksheets. I update this info daliy. Everytime I change something it slows me down waiting for all the updates. I what to make all my changes and then update my main worksheet. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update worksheet formula
Hi,
VBA intruction : Application.Worksheets("Sheet1").Range("A1").Calcu late HTH Carim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update worksheet formula
You can call .Calculate on the Application, Worksheet, or Range objects,
depending on the scope required. Sounds like you may need Range. Also, you need to look into setting the calculation mode Application.Calculation = xlCalculationAutomatic / xlCalculationManual / xlCalculationSemiautomatic NickHK "Little Penny" wrote in message ups.com... Is it possible to only update the result of a formulas on a woorksheet when a particular condition is met. I would like to have a button that when presses updates the worksheet. The reason is I have a lot of formulas referencing other worksheets. I update this info daliy. Everytime I change something it slows me down waiting for all the updates. I what to make all my changes and then update my main worksheet. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update worksheet formula
I add a couple of buttons to switch calculation mode and to calculate. This
is the code, add it to a standard code module Option Explicit Declare Function GetKeyState Lib "user32" (ByVal fnKey As Long) As Integer Private Const vkShift As Integer = &H10 Private Const CB_NAME As String = "CalculateBar" Public Sub AddCalculateToolbar() Dim oCB As CommandBar DeleteCalculateToolbar With Application.CommandBars.Add(Name:=CB_NAME, temporary:=True) With .Controls.Add .Caption = "Calculate Mode" If Application.Calculation = xlCalculationAutomatic Then .State = msoButtonUp .TooltipText = "Automatic Mode" Else .State = msoButtonDown .TooltipText = "Manual Mode" End If .FaceId = 960 .OnAction = "SetCalculateMode" End With With .Controls.Add .Caption = "Calculate Sheet" .TooltipText = "Calculate Sheet" .FaceId = 964 .OnAction = "CalculateSheet" End With .Visible = True .Position = msoBarTop End With End Sub Public Sub DeleteCalculateToolbar() On Error Resume Next Application.CommandBars(CB_NAME).Delete On Error GoTo 0 End Sub Private Sub SetCalculateMode() Dim sMode As String Dim nState As Long If GetKeyState(vkShift) < 0 Then Application.Calculation = xlManual sMode = "Manual Mode" nState = msoButtonUp Else Application.Calculation = xlAutomatic sMode = "Automatic Mode" nState = msoButtonDown End If With Application.CommandBars.ActionControl .TooltipText = sMode .State = nState End With End Sub Private Sub CalculateSheet() ActiveSheet.Calculate End Sub and then add this to build it Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteCalculateToolbar End Sub Private Sub Workbook_Open() Call AddCalculateToolbar End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code What this does is to create a toolbar with two buttons. The first sets the calculation mode, click it to set it automatic, shift-click to set to manual (it also has tooltiptext to show what the mode is). The second is used to do a manual sheet calculate if the mode is manual. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Little Penny" wrote in message ups.com... Is it possible to only update the result of a formulas on a woorksheet when a particular condition is met. I would like to have a button that when presses updates the worksheet. The reason is I have a lot of formulas referencing other worksheets. I update this info daliy. Everytime I change something it slows me down waiting for all the updates. I what to make all my changes and then update my main worksheet. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update worksheet formula
Set calculation to manual in Tools=Options, calculation tab as your default.
Then when ready to calculate, do Ctrl+Shift+F9 -- Regards, Tom Ogilvy "Little Penny" wrote: Is it possible to only update the result of a formulas on a woorksheet when a particular condition is met. I would like to have a button that when presses updates the worksheet. The reason is I have a lot of formulas referencing other worksheets. I update this info daliy. Everytime I change something it slows me down waiting for all the updates. I what to make all my changes and then update my main worksheet. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update worksheet formula
Thanks that worked...
Tom Ogilvy wrote: Set calculation to manual in Tools=Options, calculation tab as your default. Then when ready to calculate, do Ctrl+Shift+F9 -- Regards, Tom Ogilvy "Little Penny" wrote: Is it possible to only update the result of a formulas on a woorksheet when a particular condition is met. I would like to have a button that when presses updates the worksheet. The reason is I have a lot of formulas referencing other worksheets. I update this info daliy. Everytime I change something it slows me down waiting for all the updates. I what to make all my changes and then update my main worksheet. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update worksheet formula
Thanks Bob
Can I turn of the calculation for just and use a macro to update that one sheet. Bob Phillips wrote: I add a couple of buttons to switch calculation mode and to calculate. This is the code, add it to a standard code module Option Explicit Declare Function GetKeyState Lib "user32" (ByVal fnKey As Long) As Integer Private Const vkShift As Integer = &H10 Private Const CB_NAME As String = "CalculateBar" Public Sub AddCalculateToolbar() Dim oCB As CommandBar DeleteCalculateToolbar With Application.CommandBars.Add(Name:=CB_NAME, temporary:=True) With .Controls.Add .Caption = "Calculate Mode" If Application.Calculation = xlCalculationAutomatic Then .State = msoButtonUp .TooltipText = "Automatic Mode" Else .State = msoButtonDown .TooltipText = "Manual Mode" End If .FaceId = 960 .OnAction = "SetCalculateMode" End With With .Controls.Add .Caption = "Calculate Sheet" .TooltipText = "Calculate Sheet" .FaceId = 964 .OnAction = "CalculateSheet" End With .Visible = True .Position = msoBarTop End With End Sub Public Sub DeleteCalculateToolbar() On Error Resume Next Application.CommandBars(CB_NAME).Delete On Error GoTo 0 End Sub Private Sub SetCalculateMode() Dim sMode As String Dim nState As Long If GetKeyState(vkShift) < 0 Then Application.Calculation = xlManual sMode = "Manual Mode" nState = msoButtonUp Else Application.Calculation = xlAutomatic sMode = "Automatic Mode" nState = msoButtonDown End If With Application.CommandBars.ActionControl .TooltipText = sMode .State = nState End With End Sub Private Sub CalculateSheet() ActiveSheet.Calculate End Sub and then add this to build it Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteCalculateToolbar End Sub Private Sub Workbook_Open() Call AddCalculateToolbar End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code What this does is to create a toolbar with two buttons. The first sets the calculation mode, click it to set it automatic, shift-click to set to manual (it also has tooltiptext to show what the mode is). The second is used to do a manual sheet calculate if the mode is manual. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Little Penny" wrote in message ups.com... Is it possible to only update the result of a formulas on a woorksheet when a particular condition is met. I would like to have a button that when presses updates the worksheet. The reason is I have a lot of formulas referencing other worksheets. I update this info daliy. Everytime I change something it slows me down waiting for all the updates. I what to make all my changes and then update my main worksheet. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update worksheet formula
You could turn calculation mode to manual when that sheet is activated,
automatic when it is deactivated. As long as your macro does a calculate, all should be fine. Private Sub Worksheet_Activate() Application.Calculation = xlCalculationManual End Sub Private Sub Worksheet_Deactivate() Application.Calculation = xlCalculationAutomatic End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Little Penny" wrote in message ups.com... Thanks Bob Can I turn of the calculation for just and use a macro to update that one sheet. Bob Phillips wrote: I add a couple of buttons to switch calculation mode and to calculate. This is the code, add it to a standard code module Option Explicit Declare Function GetKeyState Lib "user32" (ByVal fnKey As Long) As Integer Private Const vkShift As Integer = &H10 Private Const CB_NAME As String = "CalculateBar" Public Sub AddCalculateToolbar() Dim oCB As CommandBar DeleteCalculateToolbar With Application.CommandBars.Add(Name:=CB_NAME, temporary:=True) With .Controls.Add .Caption = "Calculate Mode" If Application.Calculation = xlCalculationAutomatic Then .State = msoButtonUp .TooltipText = "Automatic Mode" Else .State = msoButtonDown .TooltipText = "Manual Mode" End If .FaceId = 960 .OnAction = "SetCalculateMode" End With With .Controls.Add .Caption = "Calculate Sheet" .TooltipText = "Calculate Sheet" .FaceId = 964 .OnAction = "CalculateSheet" End With .Visible = True .Position = msoBarTop End With End Sub Public Sub DeleteCalculateToolbar() On Error Resume Next Application.CommandBars(CB_NAME).Delete On Error GoTo 0 End Sub Private Sub SetCalculateMode() Dim sMode As String Dim nState As Long If GetKeyState(vkShift) < 0 Then Application.Calculation = xlManual sMode = "Manual Mode" nState = msoButtonUp Else Application.Calculation = xlAutomatic sMode = "Automatic Mode" nState = msoButtonDown End If With Application.CommandBars.ActionControl .TooltipText = sMode .State = nState End With End Sub Private Sub CalculateSheet() ActiveSheet.Calculate End Sub and then add this to build it Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteCalculateToolbar End Sub Private Sub Workbook_Open() Call AddCalculateToolbar End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code What this does is to create a toolbar with two buttons. The first sets the calculation mode, click it to set it automatic, shift-click to set to manual (it also has tooltiptext to show what the mode is). The second is used to do a manual sheet calculate if the mode is manual. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Little Penny" wrote in message ups.com... Is it possible to only update the result of a formulas on a woorksheet when a particular condition is met. I would like to have a button that when presses updates the worksheet. The reason is I have a lot of formulas referencing other worksheets. I update this info daliy. Everytime I change something it slows me down waiting for all the updates. I what to make all my changes and then update my main worksheet. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically update excel worksheet formula | Excel Discussion (Misc queries) | |||
some worksheet columns don't update formula calculations | Excel Worksheet Functions | |||
Automatic update formula when worksheet 1 move to another excel fi | Excel Discussion (Misc queries) | |||
update worksheet keeping formula | Excel Worksheet Functions | |||
Update a formula based on date worksheet was saved | Excel Worksheet Functions |