Worksheet change macro takes too much time when run with update list macro

**hunsnowboarder** · 02-01-2009, 08:55 AM

Hi there Everyone! I have a problem with one of my worksheets. The problem is the following:
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run. My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
Thank you in advance for your help!

**DonkeyOte** · 02-01-2009, 09:02 AM

check out Application.EnableEvents (be sure to reset to True in an Error Handler), however, you may also want to look into tweaking your Change event such that it only fires for cells within a given Intersect (if not already).

**hunsnowboarder** · 02-01-2009, 09:19 AM

HI DonkeyOte! Thanks a lot for your comment! This is what I want, I am sure! But please could you explain me how to do it as I am very very new to VBA! Both solutions would be good for me!

I think the best solution would be when worksheet_change macro activates only if there is a change in a given range (in my case 3 cells: G2, A2 and A11). Could you help me in that?

Thank you!

**VBA Noob** · 02-01-2009, 09:20 AM

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

**hunsnowboarder** · 02-01-2009, 09:36 AM

Ok VBA Noob! I think you are picking on me for something. Yes, I made a few mistakes (cross posting) in the beginnig, but (in my last posting you have also added a comment which was just to toss me) I see that now you will be allways commenting my post. This posting I put in the worksheet function forum because IT IS a worksheet related post. I think it is better for me and for you as well, if I will post no more on this site. I would like to thank you and all for your help in the past! Thank you for everything and wishing you all the best!

**VBA Noob** · 02-01-2009, 09:40 AM

Your title mentions the word macro twice and you also refer to a Worksheet change macro which in my book should be under programming.

Happy for you to post here as long as you follow the rules which you seem to be having a problem with.

PS Don't bother to thank me for moving it for you

VBA Noob