Change Event Subroutine Freezing Excel

[JohnW2]

Hi there,

I find myself having to use Excel VBA after quite a long time away from it I was once using Excel VBA 'all the time', but that was quite a long time ago! - I therefore anticipate probably having to ask further questions!), and I presume I am doing/overlooking something silly.

I am using the following (simplified) code to change the value in cell F9 when the contents of any of the cells in the worksheet are manually changed, on the basis of some fairly complicated logic regarding the values of other cells:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
if [xxxx] then
Worksheets(4).Range("F9").Value = "pppp”
elseif [yyyy] THEN
Worksheets(4).Range("F9").Value = "qqqq”
else
Worksheets(4).Range("F9").Value = "rrrr”
end if
Application.EnableEvents = True
End Sub

I had to disable and then enable events to prevent looping. The routine does as intended, in the sense of putting the correct value into cell F9, but Excel then totally 'freezes' and I can only close it by using Task Manager.

Am I missing something obvious?

Kind Regards, John

[TMS]

Having dumbed down the routine for our benefit, it looks OK. What are the conditions [xxxx] and [yyyy]? What sheet is the Worksheet Change event handler monitoring? Is Worksheets(4) a different worksheet? You should only need to disable the event handling if you are making changes to the sheet being monitored by the event handler, OR if the other worksheet (index 4) has a Worksheet Change event handler.

At face value, the WSC event handler will fire for any and every change on the sheet it is monitoring.

[JohnW2]

Trevor, thanks for your interest.

The conditions are simply based on the values of subsets of about 20 other cells - there are many ELSEIF statements, hence far more than the two (some quite complex) conditions shown in my simplified version - but, as I said, that part all seems to work 'as intended'. Everything relates to Worksheets(4).

My understanding is that the Change event handler is triggered by any change on the sheet other than a change due to Excel calculation.

What sort of situations lead to the sort of freezing I'm experiencing? I certainly had to disable the event handling. Before I did that, it again effectively 'froze', but I could see the contents of F9 flickering as it looped indefinitely. With event handling disabled, I don't see that (only the 'freezing' - so I don't think the problem now is that of recursively looping through the routine.

Kind Regards, John

[TMS]

If the worksheet being monitored and worksheets(4) are the same worksheet, you DO need to disable and re-enable event handling.

So, whatever manual, or programmatic, change you make will fire the event handler and it will always change cell F9 to one of the three values. Other than that, there appears to be no reason for the routine to loop or to freeze Excel.

[JohnW2]

Trevor, yes, as I said, I discovered empirically that I had to disable/re-enable events and, on reflection, it makes total sense - as you say, without that, every time the code changed the value of F9, the event handler would have been re-triggered again. I presume that every time a value is put into F9, that counts as 'a change event' - i.e. that the event handler is not clever enough to realise that (as would have been the case), its was 'changed' from one value to the same value, so not actually 'a change'?!

As I said, it seems that (as expected) it's now not looping, but it certainly is freezing. For fun, I exported the project as an .xls and tried it on an old machine running Excel 2000. Exactly the same thing happened.

I have learnt a few things. Firstly, it's not just Excel that is freezing, it's the whole computer - and the reason is that when we get into that state Excel is using 95-100% of the available CPU resources. I have also discovered, by moving break points around (and changing various cell values so that different conditions are true) that the freezing occurs only when the first true IF or ELSEIF condition arises (including the situation in which none of the explicit conditions are satisfied, so that the final ELSE is invoked) - i.e. when the code first changes the value of F9. Does that give you any ideas?

Kind Regards, John

[JohnW2]

As a "PS", it occurred to me that, since the problem is occurring when a value is assigned to F9, it might be that something about that cell was the culprit - for example, that cell has conditional formatting. I therefore changed the 'target cell' to a completely unused, hence 'pristine', one on the sheet - and that did not make any difference at all!

Kind Regards, John

[Norie]

I can't really see conditional formatting being the problem as it's not going to trigger/interfere with a change event.

Any chance you could upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[TMS]

A simple test demonstrates that the code works in principle.

Please Login or Register  to view this content.

I suspect the problem is elsewhere. Perhaps you have (complex) Array Formulae, or SUMPRODUCT, with full column references?

[JohnW2]

Hi Norie, thanks for your interest. Indeed, I couldn't see that either - and, as I said, redirecting the assignments to a totally 'pristine' cell didn't help. However, the problem is now solved. After confirming (as one might expect) that this code does not produce any problems ...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Worksheets(4).Range("F9").Value = "Hello"
Application.EnableEvents = False
End Sub

... it was apparent that the only material difference between this and the code which did produce problems was that, in the latter, the single assignment statement was replaced by my lengthy conditional (if...then...elseif ......... else etc.) coding, so I had a proper look at that. That code was actually a fair more complicated than I had led you to believe, and it transpired that I had, indeed, made a silly mistake! My subconscious had presumably been influenced by the programming language which I use all the time (which includes a DO...END loop) with the result that I had managed to create a loop within my conditional logic!

I'm sorry to have wasted your time, and I realise that you folk would have noticed the problem very quickly had I posted or uploaded the entire code. However, that 'conditional coding' runs to something like 60 lines of code, teeming with commercially-sensitive variable names which I would have had to 'anonymise' before posting, so I was trying to take a short cut!

Kind Regards, John

[JohnW2]

I suspect the problem is elsewhere. Perhaps you have (complex) Array Formulae, or SUMPRODUCT, with full column references?

Indeed so - see my recent post. Apologies for having wasted your time!

As I said, there may well be more questions from me over the coming days - but I hope not quite as 'silly' as this one!

Kind Regards, John

[TMS]

Fair enough, thanks for holding your hands up


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[JohnW2]

Thanks. It's certainly not the first daft thing I've done, either in writing code or in life in general

As I said, I'm sure it happened because it's quite a long time since I used VBA (or any sort of VB), yet I very frequently write code in a language which uses END at the end of any DO block, whether it be DO WHILE, DO UNTIL, iterative DO (like FOR...NEXT) or, as in this case, just a block of code which is executed only once; my mind obviously got it into its head that VBA did the same thing with 'LOOP', such that a DO...LOOP construct (without a WHILE or UNTIL) would do as I usually do with DO...END ones. At least I shouldn't make that mistake again for a good while!

Thread marked as solved, and thanks given.

Thanks again for your interest and ... watch this space

Kind Regards, John