+ Reply to Thread
Results 1 to 8 of 8

Enabling manual calculation on one specific workbook but all other open worbooks remain on

  1. #1
    Registered User
    Join Date
    03-20-2014
    Location
    greece
    MS-Off Ver
    Excel 2003
    Posts
    4

    Enabling manual calculation on one specific workbook but all other open worbooks remain on

    Guys I have a problem with a VBA code I wrote. The problem is that I want a specific workbook to be always on manual but when I open other workbooks I want them to remain on automatic even though the first workbook is set on manual through vba code. Is that possible to be done?
    This is the code I run:

    Private Sub Workbook_Activate()
    With Application
    .Calculation = xlManual
    .MaxChange = 0.001
    .CalculateBeforeSave = False
    End With
    End Sub

    Private Sub Workbook_Deactivate()
    With Application
    .Calculation = xlAutomatic
    .MaxChange = 0.001
    .CalculateBeforeSave = False
    End With
    End Sub

    Private Sub Workbook_Before_Open()
    With Application
    .Application.Calculation = xlCalculationManual
    .MaxChange = 0.001
    End With
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
    .Application.Calculation = xlCalculationManual
    .CalculateBeforeSave = False
    .CalculateBeforeClose = False
    .MaxChange = 0.001
    End With
    End Sub
    I kmow that Application.Calculation refers to all open workbooks but I don't know the code to specify the manual calculation to this workbook only while others are open. Could you help me on this??Thnx

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Enabling manual calculation on one specific workbook but all other open worbooks remai

    Application.Calculation is as it says an application level setting. You can't mix and match and have different workbooks in the same application where some are in manual calculation mode and others in automatic mode.

    All you can do is open another instance of excel as a second application.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    03-20-2014
    Location
    greece
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Enabling manual calculation on one specific workbook but all other open worbooks remai

    Quote Originally Posted by Richard Buttrey View Post
    Application.Calculation is as it says an application level setting. You can't mix and match and have different workbooks in the same application where some are in manual calculation mode and others in automatic mode.

    All you can do is open another instance of excel as a second application.
    When you say open another instance of excel as a second application what do you mean exactly?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Enabling manual calculation on one specific workbook but all other open worbooks remai

    Hi,

    I mean you'll need to click and open the Excel.exe program file a second time so that you have two Excel Applications open. One of which you'd set to Manual Calculation mode and the Other as Automatic.

  5. #5
    Registered User
    Join Date
    03-20-2014
    Location
    greece
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Enabling manual calculation on one specific workbook but all other open worbooks remai

    Is there a setting that will make excel workbooks to open in a different excel.exe?

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Enabling manual calculation on one specific workbook but all other open worbooks remai

    No. You'll just have to switch to each Excel instance when you want to open a manual or automatic calculation workbook.

    Just think of it as having two screens open each one with its own Excel App. The only difference is that you have one screen, so you'll need to toggle (ALt-Tab) between the two Excel windows.

  7. #7
    Registered User
    Join Date
    03-20-2014
    Location
    greece
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Enabling manual calculation on one specific workbook but all other open worbooks remai

    Basically I want some formulas not to calculate on their own on some specific cells but I couldn't find a way to do it. If I upload the excel file could you please imput a vba code that allows you to manually calculate on some cells only by pressing a hotkey?

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Enabling manual calculation on one specific workbook but all other open worbooks remai

    Hi,

    You can turn off manual calculation for the Excel application and then when you want to calculate a particular sheet only you could have a button which runs a single line macro

    Please Login or Register  to view this content.
    and the sheet will be calculated. However this does not meet your original requirement that other workbooks should calculate automatically. They too will also be on manual. As I said if you want to be able to handle both automatic and manual workbooks you will need two separate instances of the Excel Application.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Specific rows in several worbooks need to be extracted to a new workbook
    By patchpollito in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-07-2013, 04:21 PM
  2. On open set calculation to manual not working all the time
    By matt260384 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-03-2009, 12:47 AM
  3. Force Manual Calculation for specific area
    By chris100 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-15-2006, 02:03 AM
  4. [SOLVED] Open CSV causes calculation in manual calc mode
    By kunkletown@comcast.net in forum Excel General
    Replies: 0
    Last Post: 07-25-2006, 04:30 PM
  5. Setting Manual Calculation on Specific Cells Only
    By Jim Hagan in forum Excel General
    Replies: 3
    Last Post: 02-23-2006, 04:15 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1