+ Reply to Thread
Results 1 to 5 of 5

Why does a simple Worksheet_Calc macro only fire half the time?

  1. #1
    Forum Contributor
    Join Date
    05-24-2006
    Location
    Los Angeles
    MS-Off Ver
    2019
    Posts
    326

    Why does a simple Worksheet_Calc macro only fire half the time?

    Hi,

    I've created a Worksheet_Calculate macro that fires only half the time and I can't figure out why.

    It's supposed enter all the months into a worksheet range that are between the earliest and latest dates entered by the user on another worksheet. It's basically a nifty data validation trick for what will be source data for a pivot table: this macro insures that there will be no skipped months. So, if the user enters Jan, Feb and Apr (skipping Mar), the macro enters all 4 months between Jan and Apr on the database worksheet.

    Please Login or Register  to view this content.
    And if the user enters fewer months than what existed before, the macro enters all the inclusive months (the above macro fires) AND deletes the remaining months based on a simple last row needed calculation. Problem is, that calculation is correct but this macro wont fire and I don't know why. It will only fire when I manually adjust a cell on the worksheet.

    Please Login or Register  to view this content.
    Here is the Worksheet_Calculate macro:

    Please Login or Register  to view this content.
    I've also included the file. This has totally stumped me and any help would so appreciated.

    Thanks,

    Lawrence
    Attached Files Attached Files
    Last edited by skysurfer; 05-11-2010 at 11:47 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Why does a simple Worksheet_Calc macro only fire half the time?

    Lawrence, perhaps you should qualify the clear range to the appropriate sheet object ? As is stands the clear will fire against the active sheet.

    I would add however that whenever you disable events you should add an error handler so as to ensure that should the code debug for whatever reason Events are always reset.

  3. #3
    Forum Contributor
    Join Date
    05-24-2006
    Location
    Los Angeles
    MS-Off Ver
    2019
    Posts
    326

    Re: Why does a simple Worksheet_Calc macro only fire half the time?

    DonkeyOte,

    Qualifying the clear range to the sheet object solves this? Uh, how do I do that?

    As for error handler, would that be something like this:

    Please Login or Register  to view this content.
    Thanks for the help!

    Lawrence

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Why does a simple Worksheet_Calc macro only fire half the time?

    Quote Originally Posted by skysurfer
    Qualifying the clear range to the sheet object solves this?
    Yes.

    This

    Please Login or Register  to view this content.
    will fire against whichever sheet is active at run-time - if you wish for the above to always fire against Sheet1 (codename) as per the other routine you must qualify as such, ie:

    Please Login or Register  to view this content.
    or, if you prefer

    Please Login or Register  to view this content.
    Regards handler - looks fine to me though of course if you want to notify user / trap it then you would approach slightly differently
    For an overview of VBA error handling see: http://www.cpearson.com/excel/ErrorHandling.htm

  5. #5
    Forum Contributor
    Join Date
    05-24-2006
    Location
    Los Angeles
    MS-Off Ver
    2019
    Posts
    326

    Re: Why does a simple Worksheet_Calc macro only fire half the time?

    Got it! Works PERFECT.

    Need to get the VBA language under my belt.

    Thank you so much!!!

    Lawrence

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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