+ Reply to Thread
Results 1 to 5 of 5

Worksheet_calculate not working all the time

  1. #1
    Registered User
    Join Date
    11-08-2007
    MS-Off Ver
    2021 Prof
    Posts
    84

    Worksheet_calculate not working all the time

    Thanks to a lot of help I am getting close...I have a default sheet that has up/down arrows that changes a value.. this is liked to s second sheet where all the values change ... and thanks to the help I have WORKSHEET_CALCULATE detecting the change and running a macro....But is doesn't happen eveytime.. and that's got me stumped.

    Anyone like to help by looking at the attached and seeing if they can see whats wrong ?

    AD Faders2.xlsm

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Worksheet_calculate not working all the time

    Hi Andrew,

    The problem was my fault in the original initialization routine, which assumed which sheet would be the active sheet. Global variables were being initialized with data from the wrong sheet. See the following corrected macro (tested and working) with changes in red:
    Please Login or Register  to view this content.


    Please Login or Register  to view this content.
    Please note that the following construction is perfectly acceptable, however Excel has a limitation of 30 items. I have had problems in the past (Excel 2003) when I have had 20 items in a Union. If cells are contiguous, you might be better served by combining the contiguous cells in a single range.
    Please Login or Register  to view this content.
    Lewis

  3. #3
    Registered User
    Join Date
    11-08-2007
    MS-Off Ver
    2021 Prof
    Posts
    84

    Re: Worksheet_calculate not working all the time

    Hi Lewis.. really appreciate the post .. thank you ...now very close to my goal
    Two things which you might be able to help with however...

    You mention the Union setting .. just a little unsure how/where I include that in the code ... can you give me a little prompt

    And I often get a

    Please Login or Register  to view this content.
    .....on the cells that I have set as the Range
    This is now more of a issue as I have cells that change that I have not defined as the Range that I don't want to be calculated.

    Any thoughts


    Cheers

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Worksheet_calculate not working all the time

    Problem 2 first. I will address the Union problem in the next post.
    Please Login or Register  to view this content.
    That was probably caused by a coding error of mine, which placed the setting of the 'myRange' object in the wrong place (when the global variable array vGblSheet2CalculatedOldValues() was not initialized). That should not occur under ordinary circumstances, but will happen frequently when testing the sofware, and halting execution in the debugger.

    The following is the ORIGINAL WRONG code snippet:
    Please Login or Register  to view this content.
    The following complete macro (which moves the setting of myRange) should correct the problem:
    Please Login or Register  to view this content.
    Lewis

  5. #5
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Worksheet_calculate not working all the time

    This addresses the Union construction.

    Working extract from original code:
    Please Login or Register  to view this content.
    The output of the Debug.Print line is as expected:
    Please Login or Register  to view this content.
    If you have more than 30 items break them up into multiple sub-ranges and then do a union of the union.
    See the following (non-working) code snippet :
    Please Login or Register  to view this content.
    When developing the code, make sure the output of the debug.print line contains all the cells in your desired range, and that the range is not truncated.

    In your actual workbook, it looks to me like you probably want something like:
    Please Login or Register  to view this content.
    Please let me know if I didn't answer your question.

    Lewis

+ 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 time for working hours when start time falls outside of working hours
    By SKDY_Beau in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-20-2014, 12:50 PM
  2. Replies: 4
    Last Post: 09-03-2013, 02:52 AM
  3. Worksheet_calculate()
    By pickledmuffin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-19-2008, 08:54 PM
  4. [SOLVED] Worksheet_Calculate Question
    By Volsfan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2005, 05:05 AM
  5. [SOLVED] Worksheet_calculate()
    By Alex in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-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