Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Run macro from function key

  1. #1
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    957
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


    George J

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    957
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'll try that first thing on Monday.

    thanks
    George J

  4. #4
    Board Regular George J's Avatar
    Join Date
    Feb 2002
    Location
    Edinburgh, Bonnie Scotland
    Posts
    957
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    George J

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •