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

**ozhunter** · 03-18-2013, 04:05 AM

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

**cytop** · 03-18-2013, 04:09 AM

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.

**ozhunter** · 03-18-2013, 05:54 AM

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

**cytop** · 03-18-2013, 06:00 AM

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

**ozhunter** · 03-18-2013, 06:07 AM

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.

**cytop** · 03-18-2013, 06:15 AM

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

Another thought - is there an unmentioned formula in M5...?

**ozhunter** · 03-18-2013, 08:04 AM

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