Run macro from function key

[George J]

I have the following code to switch the sheet calculation from Manual (done to speed up another macro - copy & paste lots of detail from other sheets), to Automatic.

I am not sure how to go about it, but am sure the onkey command is correct.

Private Sub Worksheet_calculate()
Application.OnKey "{F12}", "Calculate_Auto"
End Sub


I am wanting to press F12 to revert back to
Automatic calculation for the whole sheet. I know pressing F9 refreshes the calculations, but the option is still set to manual.

Sub Calculate_Auto()
'
' Calculate_Auto Macro
' Macro recorded 28/03/2002 by gj
'

'
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

As I will be using this macro after other macros, I need it to be running in the background until F12 is pressed and the macro is run. I need this one spelled out for me I'm afraid.

Currently, pressing F12 brings up the "save as" screen.

Thanks in advance.

[John McGraw]

On 2002-04-19 16:40, George J wrote:
I have the following code to switch the sheet calculation from Manual (done to speed up another macro - copy & paste lots of detail from other sheets), to Automatic.

I am not sure how to go about it, but am sure the onkey command is correct.

Private Sub Worksheet_calculate()
Application.OnKey "{F12}", "Calculate_Auto"
End Sub


I am wanting to press F12 to revert back to
Automatic calculation for the whole sheet. I know pressing F9 refreshes the calculations, but the option is still set to manual.

Sub Calculate_Auto()
'
' Calculate_Auto Macro
' Macro recorded 28/03/2002 by gj
'

'
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

As I will be using this macro after other macros, I need it to be running in the background until F12 is pressed and the macro is run. I need this one spelled out for me I'm afraid.

Currently, pressing F12 brings up the "save as" screen.

Thanks in advance.

You just want to set up the f12 key to toggle automatic/manual calculation? Or what? Your first routine is ok:

Private Sub Worksheet_calculate()
Application.OnKey "{F12}", "Calculate_Auto"
End Sub


But it needs to be run. It looks like you are trying to put it in the calculate event right? You dont need to do that. Put it in the Workbook_Open() event, I think that would be better. (in vba click on "this workbook" and dump the following in there:


Private Sub Workbook_Open()
Application.OnKey "{F12}", "Calculate_Auto"
End Sub


Now change the calculate_auto to the following so it will toggle:


Sub calculate_auto()
If Application.Calculation = xlCalculationAutomatic Then
Application.Calculation = xlCalculationManual
Else
Application.Calculation = xlCalculationAutomatic
End If
End Sub



[ This Message was edited by: John McGraw on 2002-04-19 17:22 ]

[George J]

I'll try that first thing on Monday.

thanks

[George J]

Forgot to mention that I was puting this into a template.

Workbook open doesn't do it, but worksheet_activate does.

Sanity is restored.

thanks