Conditional Formating not applied on all Worksheet_Change events

[Oliver Vistisen]

Hi All,

I've come across an interesting issue with a macro I've been writing recently to overcome Excel 2007s bug with handling currency formats. Please find my worksheet attached.

Context: Changing number formatting in a range upon selection of a currency.

Issue: I am able to trigger a worksheet_change event both by selecting a new value from a Data Validation dropdown and by editing the cell and hitting [enter]. However, .numberformat is only applied on the latter.

Simply put, I have no idea why excel is chosing only to apply the .numberformat lines when I edit a cell directly. Events are enabled, check. Code situated in the 'Statement' worksheet, not the workbook, check. Is a _Change event, not a SelectionChange, check.



First problem which Ive encountered in vba that I cant seem to tackle or find a similar occurance elsewhere on the net. Any good ideas as how I can get the formatting to apply on the drop down change as well? Any and all help is greatly appriciated!

Thanks! Oliver

[OllieB]

Hi Oliver,

I started re-writing the Worksheet_Change code but soon discovered that I am unable to compile your workbook. I keep getting the message "Automation Error" and I am not able to figure out why. Do you also get this automation error message?

[Oliver Vistisen]

Hey Ollie, cheers for having a look

I dont get the Automation Error you're having. Do you have it with the original attachment? Sounds like a explicit object issue, such as Setting ranges and so forth.

[OllieB]

I have actually tried removing all code (copy & paste into Word document) and checked all references as well. No luck. Sorry

[Oliver Vistisen]

No problem Ollie, will keep plugging away at it. Curiosity has the better of me!

stepping away from the workbook, here is just the code in question:

Please Login or Register  to view this content.

[OllieB]

I looked at it and do not understand why you have so many ranges which are offset on each other. Would it be possible (if you feel like it) to just write in plain English what you would like to happen. Which cell(s) do you want to monitor and what actions would you like the code to perform. I have a feeling this can all be written much more simple (no disrespect intended)

[Oliver Vistisen]

For the moment the Ranges are not an issue. We can effectively ignore those as I've narrowed it down to the Workbook, not the code.

I wrote the following basic version of what I'm doing and it works flawlessly in a new Workbook, however does not work within mine.

Please Login or Register  to view this content.

My original question was: Why is the Change event only applying formatting when the target cell is edited and [enter] selected, rather than during all change events (e.g. a drop down list change)

Now my question is: What is different in my workbook that causes this phenomenon.

Oliver

[Oliver Vistisen]

Some work last night narrowed down the cause of the issue.

An Evaluate function I use for the conditional formatting (code below) seems to interfere. As soon as that code is removed from my Module the currency formatting is applied during all change events: problem gone!

Please Login or Register  to view this content.

Now to come up with a way of re-implimenting my Evaluate function without causing the same issue!

Oliver

[OllieB]

One thing I noticed, you have declared the Eval as a function yet you have not declared any return variable type. Is that even allowed?

[Oliver Vistisen]

I've tried declaring the function as a string however there is no change to its application or the _Change event issue.

A workaround may have to be to remove the conditional formatting in cells using the Eval function, run the cange event, then reapply it. Heck of a work around :S

[Oliver Vistisen]

Workaround was far easier than first thought and all works dandy.

Used Chip Pearsons method of a public boolean variable to restrict the triggering of the Eval function. It now looks like this:

Please Login or Register  to view this content.

All Done

Thanks for the to-and-fro Ollie, been a good sounding board ^^

Oliver

[OllieB]

You are very welcome. Still am a bit sceptical about the number of ranges and offsets you were using in the original code. I think you underlying requirement could be fulfilled in a simpler manner.

[Oliver Vistisen]

I am certain it can be cleaned up, just a question of priorities at the moment.