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

Update worksheet formula
 

Hi,

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

HTH
Carim

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

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

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

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

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

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