Weird event issues

[JuliusUmulius]

Hello guys and gals. I got this weird situation that I hope someone can help me resolve. I have tried a lot of googling, but am unable to find a solution. The web is flooded with people not understanding Application.EnableEvents, so it is a bit difficult to find relevant information. I can assure you that EnableEvents is indeed turned on, so that is not the issue.

The case is this: I got this complex Excel application with several worksheets. In one of these worksheets I have Worksheet_BeforeDoubleClick, Worksheet_Change and Worksheet_SelectionChange events.
All of them worked fine until suddenly I began having problems with the Worksheet_Change event:

1. Breakpoints doesn't work any more when I change values in cells or paste. But if I clear values (press delete) it does.
2. Even with breakpoints inserted, some code below breakpoints run. Like debug.print.
3. I have, by thorough debugging, noticed that the procedure suddenly exits at some point without any obvious reason. In one occasion it just ended after

Application.ScreenUpdating= True

, not continuing to the following line which was

Application.EnableEvents = True

.

What I am trying to do is to capture any paste operations and clean out the formatting. The other procedures within the Worksheet_Change event is very simple and seems to work just fine.

I have tried cleaning the workbook with Clean VBA Project from Ribbon Commander. Although it seems to solve some stability issues, it doesn't manage to solve this.
I have also tried to comment out all other events, parts of the code, deleting the code and build it up again from scratch, compiling the code from the Debug menu, etc... I just can't seem to kill this bug.

Any help is deeply appreciated. Thanks.

 

[AlphaFrog]

Is there any event procedures in the ThisWorkbook code module?

You're probably aware that the interation of Worksheet_Change and Worksheet_SelectionChange events can trigger each other depending on the code. It's a common problem in the forums. It's hard to say if that's the case here without seeing your code.

If you use On Error Resume Next, that may be hiding code errors.

 

[JuliusUmulius]

Thanks for the reply AlphaFrog. I digged a bit furter and have narrowed it down to conditional formatting. Some rule is screwing up the events. I noticed how some formatting blinked very briefly when I changed content of a cell. I then tried to remove all conditional formatting and as by magic it all works like intended. Next is to find the culprit!

 

[JuliusUmulius]

It looks as if custom functions and conditional formatting don't mix. However simply my UDF is, it still messes up events. Are there some special techniques that I have to apply in order for them to work correctly, or do I just have to accept that I can't have that complex condition formulas?

This is the function that I use in my conditional formatting rule. Am I doing something here that should corrupt my events? If i just leave IsValidForUpload = False in there everything works fine.

Function IsValidForUpload(valS As Range, valArticle As Range, valPrice As Range, valTotal As Range) As Boolean
' Function to check if article row is valid for upload
    
    IsValidForUpload = False


    If (valS = "A" Or valS = "D" Or valS = "W") And _
            Not IsEmpty(valArticle) And _
            valPrice <> 0 And _
            valTotal > 0 Then


        IsValidForUpload = True
    End If


End Function

(I tried a version with error trapping as well, with no success)

 

[AlphaFrog]

I don't know why that would corrupt your events.

Does it work if you don't use your UDF in the CF formula?

CF Formula (change the variable names to cell references)
=AND(OR(valS="A", valS="D", valS="W"), valArticle<>"", valPrice<>0, valTotal>0)

 

[JuliusUmulius]

I don't know why that would corrupt your events.

Does it work if you don't use your UDF in the CF formula?

CF Formula (change the variable names to cell references)
=AND(OR(valS="A", valS="D", valS="W"), valArticle<>"", valPrice<>0, valTotal>0)

That solved it! I'm not that experienced with the use of AND and OR in formulas like that, so thank you AlphaFrog! Brilliant!

 

[AlphaFrog]

You're welcome. Thanks for the feedback.