Link to home
Start Free TrialLog in
Avatar of Jeremy Campbell
Jeremy CampbellFlag for United States of America

asked on

Conditionally change backround color of detail section when [TimeInspStart]<>"" ?

I have used textboxes moved to the back of the detail section for this before but it is not going to work for this particular situation as a user can easily accidentally click on the backround and cause the box to become active. (When setting conditional formatting on a textbox that has Enabled set to false, when it the conditional formatting applies a color to the box, it then becomes enabled and allows the users to click on the box.

I just want to set some kind of vba for the form that will highlight the line in the detail section whenever my different criteria is met.

So maybe something like whenever [TimeInspStart] <> "" then change the backround color blue.

Thanks in advance for the advice.
Avatar of mbizup
Flag of Kazakhstan image

Have you tried setting Locked = True/Enabled = False on your background textbox?

That should prevent the user from activating it.
Avatar of HomerTNachoCheese

Try placing your code in the form's On Current event.
Avatar of Jeremy Campbell


Yes, that is how I have the txtbox setup, which when the formatting is not being applied does not allow them to click on the box. However, when the conditional formatting is met and it changes the color of the box then the box becomes enabled and the users can accidentally click on it.
On current highlights everysection of the entire form if the condition is met anywhere on the form. So does on load.

Or at least it does using the following code;

Private Sub Form_Current()

If [TimeInspStart] <> "" Then

Me.Detail.BackColor = vbRed
End If

End Sub
Is this continuous forms, multiple records on the screen then?

You posted that somehow when conditional formatting is applied that users can get to that box you do not want them to get to.  Why not put in the On Current or other events some code to lock and disable the box?  I am not sure why conditional formatting would change the locked/disabled properties, but if it really is, then insert code to force those settings back.
I will give that a try. But yes, it definitely is somehow reenabling the textbox once the formatting gets applied.
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Jim, that's fantastic.. I guess you could say its a workaround but it does the trick beautifully!
Homer.. FYI I also tried setting the enabled back to false on the on current event and that did not take care of it.
I dont think you can do what you're asking if the form is in continuous view, but it it is in single form view, you can do this, in the current event as suggested by Homer:

If  [TimeInspStart] <> "" then me.detail.backcolor = vbblue

OR stick with the Textbox idea and include 'disabled' as a format choice when said condition is met (You'll see this option as a greyed out button in the CF window).
mbizup, I tried what you said there intially and didn't get it to work because when I clicked the disabled box it wiped out my color formatting.. I didn't realize it was going to let me reapply the color formatting even though it is disabled so I gave up on that. Just tried it though and it actually will let you first select the disabled box and then the color.

Sorry I already creditted Jim.. I figured I was pretty much out of options.. I can reopen and send some points your way..

Thanks all for the help though!
Don't worry about it - I was a little late with my reply and neglected to refresh the screen.

It's a workaround too.

Surprising to me that CF toggles the enabled property like that when you are not telling it to.  Learned something new here.
Ok.. Thank again though mbizup.. Yeh I thought that was odd myself and I tried every combination of the enabled and locked properties that I could.. Was kind of frusterating me.. :) Glad to have some options though.