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
|