+ Reply to Thread
Results 1 to 9 of 9

VBA On Worksheet Calculate not working

  1. #1
    Registered User
    Join Date
    03-10-2010
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    63

    VBA On Worksheet Calculate not working

    Good Day,

    I have a snippet of VBA code that resides in my worksheet under the "On Worksheet Calculate" trigger. Basically, the code is supposed to run whenever the worksheet calculates anything.

    Yesterday it was working fine, now when I open the file this morning, the macro isn't running at all.

    If I manually run the macro from within the VB window, it runs correctly, but it doesn't run when the worksheet recalculates like it supposed to.

    If anyone could provide any insight into this issue, it would be greatly appreciated.

    Regards,
    Chris

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: VBA On Worksheet Calculate not working

    Have you checked events are enabled?

    You can do that be entering this in the Immediate Window (CTRL+G).
    Please Login or Register  to view this content.
    If that returns False events are disabled, you can re-enable them with this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    03-10-2010
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: VBA On Worksheet Calculate not working

    I tried that, and events are enabled.

    Perhaps its something in my methodology. I am trying to trigger a macro that executes when a "group" is opened or closed. I was told that whenever a group is opened or closed, it forces a worksheet calculation, and I can write code that executes on worksheet calculation. I did that, and it worked perfectly yesterday. Now opening or closing the group doesn't seem to be triggering a worksheet calculation.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: VBA On Worksheet Calculate not working

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  5. #5
    Registered User
    Join Date
    03-10-2010
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: VBA On Worksheet Calculate not working

    Okay, I've attached a sample

    In this workbook, Cell H1 has text in it that should change from white to black when the grouping is closed, and back to white when the grouping is opened.

    This worked perfectly for me yesterday, but it isn't working now.

    Thanks,
    Chris
    Attached Files Attached Files

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: VBA On Worksheet Calculate not working

    In that workbook the Calculate event isn't being triggered when on group/ungroup.

    When it was working did you have any formulas on the sheet?

  7. #7
    Registered User
    Join Date
    03-10-2010
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: VBA On Worksheet Calculate not working

    I did, and I do on my actual sheet now as well. The only thing that changed since it was working yesterday is that I saved the sheet, closed it, and reopened it today.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: VBA On Worksheet Calculate not working

    If I put a simple formula, eg =NOW(), then the Calculate event is called when grouping/ungrouping.

    This might be a stupid question but do you have Automatic Calculation turned on?

  9. #9
    Registered User
    Join Date
    03-10-2010
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: VBA On Worksheet Calculate not working

    Yes, automatic calculation is turned on. Following your logic, I added an = now() calculation to my sheet, and now it works. That doesn't make sense to me though, as there is an entire row of sum calculations that should also trigger the event.

    I suppose for now I will just hide the =now() somewhere on the sheet and try to pinpoint the root of the problem later.

    Thanks for the 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. [SOLVED] calculate not working, or just sometimes
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2015, 05:09 PM
  2. Replies: 2
    Last Post: 06-14-2013, 10:45 AM
  3. Replies: 9
    Last Post: 03-08-2013, 11:50 AM
  4. Calculate 7th Working day
    By antoanand1981 in forum Excel General
    Replies: 3
    Last Post: 10-29-2012, 02:18 AM
  5. Working Hr. calculate
    By Rahul Nagar in forum Excel General
    Replies: 5
    Last Post: 06-16-2009, 10:44 AM
  6. Replies: 1
    Last Post: 06-29-2005, 08:05 AM
  7. [SOLVED] Worksheet Change event code moved to Worksheet Calculate event... and it's not working
    By KimberlyC in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-23-2005, 06:05 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