Results 1 to 7 of 7

Thread: F9

  1. #1
    Neophyte g48dd's Avatar
    Join Date
    Jun 2011
    Location
    Winnipeg, Canada
    Posts
    3
    Articles
    0

    F9



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Excel 2003: I need to recalculate after I run a macro, is there a line I could put at the end of my macro that would do this for me, instead of having to use F9?

    Thank you
    Ken

  2. #2
    Add this code at the end of your macro:

    With ActiveSheet
    .EnableCalculation = False
    .EnableCalculation = True
    .Calculate
    End With

  3. #3
    Neophyte g48dd's Avatar
    Join Date
    Jun 2011
    Location
    Winnipeg, Canada
    Posts
    3
    Articles
    0
    Hello Jonix, I can usually adjust things so they work but I am having problems with this, I am getting can not execute in break mode. This is what the end of my code looks like:
    Code:
        Set rngCells = Union(rngCells, Range("A2677,A2696,A2699,A2731,A2765,A2768,A2800,A2803,A2835,A2838,A2869"))
        Set rngCells = Union(rngCells, Range("A2884,A2887,A2919,A2922,A2941,A2944,A2955,A2958,A2977,A2980"))
        
        With rngCells.Interior
            .ColorIndex = 37
            .Pattern = xlSolid
            
    With ActiveSheet
    .EnableCalculation = False
    .EnableCalculation = True
    .Calculate
    End With
        
    End Sub

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Does that code compile? (Debug-->Compile VBA Project)

    It looks to me like you're missing an "End With" right after the .Pattern=xlSolid

    Jonix, curious why you turn calculation off, then on, then calculate? I would have thought Activesheet.Calculate would be sufficient? Have you found that not to be the case?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Neophyte g48dd's Avatar
    Join Date
    Jun 2011
    Location
    Winnipeg, Canada
    Posts
    3
    Articles
    0
    Absolutely, that was it, runs fine.

    Thank you
    Everyone
    Ken

  6. #6
    Quote Originally Posted by Ken Puls View Post
    Jonix, curious why you turn calculation off, then on, then calculate? I would have thought Activesheet.Calculate would be sufficient? Have you found that not to be the case?
    Hey! I found this in official MSDN Tested in my Excel and it worked - for entire workbook. Thats interesting because "ActiveSheet" normaly is not all sheets in workbook.. but it works (recalculates) for all sheets.

    I decided to keep the original Microsoft advice - just in case. Maybe on some Excel versions it is required?

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Weird...

    I can see why it would work that way. I've always forced my workbooks/worksheets to calculate using the following (depending on what I needed.)

    Code:
    Application.Calculate
    ActiveSheet.Calculate
    Range.Calculate
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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