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,090
    Articles
    79
    Blog Entries
    14
    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)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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,090
    Articles
    79
    Blog Entries
    14
    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)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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
  •