Long Calculation Loop Upon Update

Hi Everyone;

My current task is to create a spreadsheet that will auto-hide/show rows based upon values in other cells. I am currently about halfway 40% complete with this aspect of the file. I've read over many forums, and the best what that I've seen individuals do it on other sample spreadsheets posted were a =Rand() in a random cell somewhere in the sheet, and a Private Sub worksheet_calculate()

I'm far from a VBA expert. I'm trying to learn as I go along. Here is my current VBA:

Please Login or Register  to view this content.

It was only when I added the

Please Login or Register  to view this content.

clause that it began to do a 5 second long update process upon any cell value changing.

I'm looking for how to set my VBA code to automatically update Hidden and unhidden fields, but without this 3 second updating calculation pause. Any help/advice?

Hello Miraun,

Whenever you right code for a worksheet event like Calculate, Change, or SelectionChange, you should disable the event so it doesn't retrigger when your code modifies the worksheet. Use Application.EnableEvents to turn the events on and off by setting the statement True or False, respectively.

Please Login or Register  to view this content.

And whenever disabling events it's always a good idea to put in place an error handler so as to ensure events are always enabled upon completion regardless of failure/success of the routine as a whole.

You do this in the same sub, ie one approach...

Please Login or Register  to view this content.

For more info on error handling see: http://www.cpearson.com/excel/ErrorHandling.htm