+ Reply to Thread
Results 1 to 7 of 7

Allow code to run once then not until it's trigger value appears again

  1. #1
    Registered User
    Join Date
    06-04-2011
    Location
    NSW Australia
    MS-Off Ver
    Excel 2007
    Posts
    43

    Allow code to run once then not until it's trigger value appears again

    Hello

    I have the below code in a worksheet

    Please Login or Register  to view this content.
    It works as required, but too well. While ever M5=40 it runs printPAB30 constantly, as it should.

    What can I do to the code to make it run once, when M5=40, then wait till m5 value changes, before looking for 40 in m5 again and running printPAB30 again when M5 reaches 40.

    Cheers

  2. #2
    Forum Expert (Lazarus)
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    .
    Posts
    2,594

    Re: Allow code to run once then not until it's trigger value appears again

    Move your code to the WorkSheet_Change event. Then you can check which cell has changed and its current value...
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-04-2011
    Location
    NSW Australia
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Allow code to run once then not until it's trigger value appears again

    Thanks cytop. Your code errors, I think because there are other calculations via formula going on on the sheet.

    My code works, I just need a way to have it work once, then "go to sleep", unitl M5 = 40 again

  4. #4
    Forum Expert (Lazarus)
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    .
    Posts
    2,594

    Re: Allow code to run once then not until it's trigger value appears again

    I think it's because what the procedure that is called does... but I can't see that code.

  5. #5
    Registered User
    Join Date
    06-04-2011
    Location
    NSW Australia
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Allow code to run once then not until it's trigger value appears again

    When A5=40, the code below is called, which activates another sheet to print it.

    Please Login or Register  to view this content.
    Maybe I should change the code that returns 40, to the "bulk" sheet, and put your code in that sheet instead of the one I have it now.

    Thanks for your persistence.

  6. #6
    Forum Expert (Lazarus)
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    .
    Posts
    2,594

    Re: Allow code to run once then not until it's trigger value appears again

    Works fine for me... What error is raised?

    Another thought - is there an unmentioned formula in M5...?
    Last edited by cytop; 03-18-2013 at 06:54 AM.

  7. #7
    Registered User
    Join Date
    06-04-2011
    Location
    NSW Australia
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Allow code to run once then not until it's trigger value appears again

    Sorry for the delay

    I'll do my best to explain as fully as I can

    Cell M5 gets it's data via a count formula. When there are 40 entries on the "bulk" sheet, it needs to print.

    I shifted your formula to the "bulk" sheet that will be printed. (Now I don't get any errors, but it doesn't fire the "printPAB30" macro to print the "bulk" sheet)There are a good number of formulas on the sheet that change as the data is processed into the sheet, but no physical changing of cells like copy or paste.

    The "bulk" sheet gets it's data from a sheet called "bulk data". When a button is pushed, there are about four operations that take place on the "bulk data" sheet, including copying and pasting via vba (lots of cell changes)

    I am trying to automate the printing of the "bulk" sheet when there are 40 entries
    Last edited by ozhunter; 03-18-2013 at 08:11 AM.

+ 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