Cancel=True not working on one worksheet in a workbook

[kgartin]

I have a workbook with multiple worksheets (Jan - Feb), each with a load of data. Each sheet is set up to change colors (among other things) if a cell is double-clicked and each private sub ends with cancel=true. On one of the sheets (Feb), it just doesn't work (although it HAS worked on that sheet until recently...no relevant changes have been made). All the other sheets function properly and do not go into the cells edit mode when double-clicked. February goes into edit mode every time. The code is the same for each sheet.

Any ideas why this is happening?

 

[kgartin]

I was using it as a conditional format for a range of cells - IE

=and(isformula($F7),$AM7=$F7)

But even without that formula listed in conditional formatting the function itself was causing the sheet to behave improperly (when you double click a cell, it would ignore cancel=true and go into cell edit mode. It would basically ignore the last lines of code after "end with" which was where cancel=true was located). Very weird. No idea why.

 

[ZVI]

Maybe it's the Russians...

Russians suggest to not use any VBA functions in CF
It hangs Excel

 

[kgartin]

Lol! Thanks товарищ!

 

[ZVI]

Lol! Thanks товарищ!

Each cell with UDF in CF calls that UDF numerous times
You may set break point on that UDF via F9 to check this behavior, but apply UDF to CF of one cell only to not hang Excel for a long time

Some suggestions:
1. Try moving this line of the code Cancel = True to the top of the Sub Worksheet_BeforeDoubleClick to be sure it will play anyway.
2. In the early release (pre SP2) of Excel 2007 a double click event did not work in case dropdown list was used in the cell. Hope it's not your case, but please test a double click event on cells without dropdowns.
3. ISFORMULA cell function comes with Excel 2013 which can be used safely in CF in Excel 2013/2016.