+ Reply to Thread
Results 1 to 6 of 6

VBA to open a workbook with manual calculation and close with automatic calculation

Hybrid View

thedunna VBA to open a workbook with... 03-03-2019, 01:48 PM
bakerman2 Re: VBA to open a workbook... 03-03-2019, 08:41 PM
thedunna Re: VBA to open a workbook... 03-04-2019, 10:51 AM
bakerman2 Re: VBA to open a workbook... 03-04-2019, 11:31 AM
thedunna Re: VBA to open a workbook... 03-04-2019, 11:47 AM
bakerman2 Re: VBA to open a workbook... 03-04-2019, 12:28 PM
  1. #1
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    334

    VBA to open a workbook with manual calculation and close with automatic calculation

    I have a very large workbook with several worksheets. All have extensive formulas and conditional formatting. Data entry is very, very slow. Is there any code that I can add to my existing VBA that would open the workbook set to manual calculations so that data entry is not bogged down? And then close (or save) the workbook by reactivating calculations to automatic? My current VBA code is listed below. Thanks to any help you could provide.

    Private Sub CommandButton1_Click()
    
    Worksheets("Master Membership Records").Range("B5:B525").ClearContents
    
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim Xrw As Long, Lr As Long
        If Target.Row = 3 Then
            Application.EnableEvents = False
            Lr = Range("A" & Rows.Count).End(xlUp).Row
            Range("A5", "Q" & Lr).Sort key1:=Cells(5, 1), Header:=xlNo
            Xrw = Range("A:A").Find("X", , , , , , False, , False).Row
            Range("A5", "Q" & Xrw - 1).Sort key1:=Cells(4, Target.Column), Header:=xlNo
            Range("A" & Xrw, "Q" & Lr).Sort key1:=Cells(4, Target.Column), Header:=xlNo
            Application.EnableEvents = True
        End If
    End Sub

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,274

    Re: VBA to open a workbook with manual calculation and close with automatic calculation

    Maybe you could try something like this.

    In Thisworkbook-module.
    Private Sub Workbook_Open()
        Application.Calculation = -4135 'xlCalculationManual
    End Sub
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Application.Calculation = -4105 'xlCalculationAutomatic
    End Sub
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    334

    Re: VBA to open a workbook with manual calculation and close with automatic calculation

    bakerman2, that did not do anything. Unless I am placing it in the wrong way. I am a novice.

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,274

    Re: VBA to open a workbook with manual calculation and close with automatic calculation

    In the left pane of the VBEditor you have all the sheetnames listed below eachother.
    As last one in the list you will see ThisWorkbook. open that and pase the code in the right pane.
    Save your workbook, close it and reopen it.

  5. #5
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Tacoma, Washingtonn
    MS-Off Ver
    Excel 2019
    Posts
    334

    Re: VBA to open a workbook with manual calculation and close with automatic calculation

    Wonderful solution! Thank you so much bakerman2!

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,274

    Re: VBA to open a workbook with manual calculation and close with automatic calculation

    You're welcome, glad to help.

+ 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. Enabling manual calculation on one specific workbook but all other open worbooks remain on
    By teo.paranoid in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-25-2014, 06:39 AM
  2. [SOLVED] Automatic Calculation Keeps Changing To Manual
    By timbo1957 in forum Excel General
    Replies: 3
    Last Post: 05-08-2013, 04:11 AM
  3. Automatic Workbook Calculation switching to manual
    By Gunther Maplethorpe in forum Excel General
    Replies: 0
    Last Post: 08-24-2011, 12:35 PM
  4. Automatic Calculation:manual.
    By Boxman in forum Excel General
    Replies: 1
    Last Post: 02-26-2007, 02:07 PM
  5. Automatic vs Manual Calculation
    By bullwinkle55423 in forum Excel General
    Replies: 3
    Last Post: 01-29-2007, 06:02 PM
  6. Automatic Calculation switching to manual
    By gstevens26 in forum Excel General
    Replies: 4
    Last Post: 06-06-2006, 04:34 AM
  7. Replies: 2
    Last Post: 05-31-2006, 09:10 AM
  8. Manual / Automatic Calculation
    By Adam1 Chicago in forum Excel General
    Replies: 1
    Last Post: 02-06-2006, 06: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