+ Reply to Thread
Results 1 to 4 of 4

Custom function not firing when a change to the workbook/worksheet has been done

  1. #1
    Registered User
    Join Date
    04-15-2013
    Location
    Hobbsville, NC
    MS-Off Ver
    Excel 2007
    Posts
    3

    Question Custom function not firing when a change to the workbook/worksheet has been done

    Hello all,

    This is my first time posting here and am glad to meet everyone. I am having a problem with a custom function that I wrote to return a double value based on worksheets that are in the workbook giving me a total. I wrote the function to be used in the sheets cells thinking that it would automatically update as the scenario's/numbers change but it only seems to update during certain changes and I don't know how to get it to fire off everytime there are changes.

    Here is the code:

    Please Login or Register  to view this content.
    The following is the in cell formula that I give it to produce the numbers:

    Please Login or Register  to view this content.

    So the cells seem to update when I add new sheets to the workbook which is great but when I rename the workbook to the proper name it does not fire off the update in the calculation sheet. I can goto each cell that has that forumla in it and hit enter and it will update that individual cell but that's a royal pain in the **** :/

    Any help or guidance with making it fire off when you rename a sheet updating the all of the forumulas would be great.

    Thanks for your time,

    TheChazm

    Edit 1:
    Also when I update the numbers from the source sheets manually it still does not update the calculations... dang :/
    Last edited by thechazm; 04-15-2013 at 05:33 PM.

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Custom function not firing when a change to the workbook/worksheet has been done

    Hi

    Try putting in
    Please Login or Register  to view this content.
    into your function and see if that forces it to change. Note that this could add some overhead to your workbook, as it should process with EVERY change in the workbook.

    rylo

  3. #3
    Registered User
    Join Date
    04-15-2013
    Location
    Hobbsville, NC
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Custom function not firing when a change to the workbook/worksheet has been done

    Thanks I'll give it a shot and repost the results

  4. #4
    Registered User
    Join Date
    04-15-2013
    Location
    Hobbsville, NC
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Custom function not firing when a change to the workbook/worksheet has been done

    Works Great! Only takes seconds to update all of the value's from all of the sheets and that's dealing with many formula's over 76,000 rows of data that it's looking at in multiple sheets :D

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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