Conditionally formatting ActiveX TextBox based on value

Hi

I have a spreadsheet with many ActiveX Textboxes which values are linked to a cell on another sheet. What I'm trying to do is change the back colour of the Textboxes based on their value to create a Traffic Light Report ie when the value changes so to does the back colour.

The reason I'm trying to do it this way is so that I can move the Textboxes and change their shape to create a map as such. The map often changes, so much easier to move and reshape a Textbox rather than cells which is what we currently do. Also having issues with the current cell model as many cells have been merged to try and create a map that is visually accurate.

To start with I've tried just playing with one Textbox using the text box change event and a couple of other methods but haven't had any luck. If I could get that to work I was then going to try and figure out how to apply to all textboxes at once.

Any help/suggestions would be much appreciated!

Thanks

Hi there,

Take a look at the attached workbook and see if it does the sort of thing you need.

It uses the following code:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg

Thanks so much for that, I've had a play with the attachment you sent. I linked the text box to a cell on the same sheet but when I change the cell the textbox colour doesn't change. However when I change the value within the text box and click elsewhere it does work. I've changed the function from 'Lost Focus' to 'Change' and this seems to work a treat.

I've tried to take it a step further and replace 50,100,150 etc with a value from another sheet (Sheet1.Range("B20").Value) but this seems to cause an issue and the colour no longer changes. Any idea where I'm going wrong?

Thanks
Jo

Hi again Jo,

Many thanks for your feedback.

Ok on using the Change event rather than the LostFocus event to trigger the colour change. Whenever possible I avoid using the Change event for the simple reason that the event is triggered each time any change is made to any character displayed in the TextBox rather than when all changes have been completed, but in some situations that's exactly what's needed.

As far as your second point is concerned (it's interesting to see that you use the worksheet CodeName - good for you!! ) it seems that Excel VBA finds a statement such as "Case Is < wksThresholds.Range("B1").Value" to be "too complex", as demonstrated by the following code:

Please Login or Register  to view this content.

The attached workbook uses the following version of the previous routine, and it seems to work:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg

Thanks so much, this is working well! Thanks for explaining about the Lost Focus too, I make this stuff up as I go along so always great to have someone who knows what they're doing explain these details to me so I can store them away for future use!

Thanks again,
Jo

Hi again,

Thanks for your feedback.

You're welcome - glad I was able to help.

Regards,

Greg M