Run Time on Delete

**zimbo109** · 03-21-2010, 05:57 AM

Hi,

The attached WB has 2 unlocked areas (yellow/red), and to unlock a row the user inserts a "#" into column A (yellow)- works fine. The delete button deletes an entire row- works fine, however the problem is that when a user deletes a line directly above the # line the thing hangs for quite some time and I dont know why. Is there some conflict between the two codes?

Thanks
Chris

**JBeaucaire** · 03-21-2010, 06:45 AM

Try this... I moved the protection controls into a worksheet_activate macro which gives all other macros permission to run even though the sheet is protected...that's the UserInterfaceOnly flag.

Then on the button macro we just turn off other macros while we're deleting so it doesn't trigger the worksheet_change macro that self-runs on the same sheet.

**tony h** · 03-21-2010, 06:48 AM

Hi,
I put a debug.print line in at the beginning and end of the subroutines to see what was happening

This was from a delete. The button event runs and causes the change to run.
on the "change ... end line " the number is the nuber of times the cell.EntireRow.Locked = False line is executed.

10:39:45 button $A$9
10:39:47 change $9:$9
10:39:47 change 0 ... end no changes on a normal delete
10:39:47 button ... end

entered #
10:39:55 change $A$8
10:39:55 change 1 ... end # entered

delete on line above #
10:40:02 button $A$7
10:40:04 change $7:$7
10:40:14 change 16384 ... end delete on line above #
10:40:14 button ... end

So your problem is clearly shows up here!

Please Login or Register  to view this content.

**zimbo109** · 03-21-2010, 07:06 AM

Thanks for your replies, however - Jerry I recieve an error "unable to set the locked property of the range class"
Tony, still have the same runtime?

Regards
Chris

**zimbo109** · 03-21-2010, 07:33 AM

Jerry,

I have got your version working!! not sure what I did - but it works!!

Thanks Again
Chris

**tony h** · 03-21-2010, 07:36 AM

Originally Posted by zimbo109

Tony, still have the same runtime?

As your original question was "is there some conflict between the code?" I only posted information that would enable you to understand the problem that was occuring and allow you to determine a solution .. for which there are many.

Why do you think you are getting 16384 loops of the code?

**zimbo109** · 03-21-2010, 07:45 AM

Thanks Tony, I understand that now you have highlighted it. I have used Jerrys code to let one macro run whilst the other is not activated.

Thanks again
Chris