Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Action only during Excel save? (Excel2003 VBA)

    I want to switch calculation off during saving of my Excel sheet (but back on to Automatic, immediately afterwards). Now I know I can trigger events before saving (WorkBookBeforeSave), but there does not seem to be an 'aftersave' event. Any ideas?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Action only during Excel save? (Excel2003 VBA)

    Schedule an ontime event that triggers a sub in a normal module:

    Application.OnTime Now(), "TurnCalcBackOn"

    In normal sub:

    Sub TurnCalcBackOn()
    Application.Calculation=xlCalculationAutomatic
    Application.CalculateBeforeSave=true
    End Sub

    Remember to also turn off calculatebeforesave before the save...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Action only during Excel save? (Excel2003 VBA)

    Thanks,

    Let me see if I understand you... I schedule the ontime in the Beforesave event?

    Then, in the same BeforeSave even, I turn of "calculatebeforesave".

    The "Now()" will only fire AFTER the save is complete? (I want this because the save calculates through many charts and takes time).

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Action only during Excel save? (Excel2003 VBA)

    Yes, schedule it within the beforesaveas event.

    The scheduled ontime event will fire after the beforesaveas event has finished AND Excel has finished the save. All routines called from the beforesaveas event directly will have finished too.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Action only during Excel save? (Excel2003 VBA)

    Sorry for the delay, implementing now. One question... the Sub TurnCalcBackOn will be visible right? If I'd make it "Private" the Workbook BeforeSave event wouldn't see it anymore... So is there a way to make it invisible?

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Action only during Excel save? (Excel2003 VBA)

    Hi Erik Jan

    The TurnCalcBackOn procedure will not be visible if you add an argument as shown below:

    Sub TurnCalcBackOn(Optional Abort as Boolean)
    Regards
    Don

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Action only during Excel save? (Excel2003 VBA)

    Another way to make it invisble is by inserting
    Option Private Module
    at the top of the module.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Action only during Excel save? (Excel2003 VBA)

    Thank you Jan Karel
    I see now that I have created a lot of inefficient code in the past. As always, your guidance is much appreciated.
    Regards
    Don

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Action only during Excel save? (Excel2003 VBA)

    Your solution isn't worse than mine, I just posted an altenative.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Action only during Excel save? (Excel2003 VBA)

    I beg to differ Jan Karel. On a mid sized project with many procedures to be excluded from visibility, your solution is much more efficient,
    Regards
    Don

Posting Permissions

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