Update worksheet formula

[Little Penny]

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

[Carim]

Hi,

VBA intruction :
Application.Worksheets("Sheet1").Range("A1").Calculate

HTH
Carim

[NickHK]

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" <raz...@gmail.com> wrote in message
news:1161343595....@h48g2000cwc.googlegroups.com...

[Bob Phillips]

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" <raz...@gmail.com> wrote in message
news:1161343595....@h48g2000cwc.googlegroups.com...

[Tom Ogilvy]

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]

Thanks that worked...

[Little Penny]

Thanks Bob

Can I turn of the calculation for just and use a macro to update that
one sheet.

[Bob Phillips]

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" <raz...@gmail.com> wrote in message

news:1161358985....@k70g2000cwa.googlegroups.com...