Forum Discussion
Text Box Conditional Formatting
Hi
I have a database of circa 100 audit questions and for each answer is a combo box for Yes: meets the standard, No: Minor NC, No: Major NC, OFI and N/A. I would like to be able to format the fill colour of a text box below each question based on the value of the combo box field (traffic light system) with grey for null or "N/A" and wondered of there is a simple solution for this. So far, I can only find conditional formatting as a solution but this requires 5 different formats for each individual question.
I'd very much appreciate being pointed in the right direction.
Thanks.
There are many ways to archive this, but conditional formatting is fine.
I assume you have copied all desired formatting from the text field Question001 (or similar) to the other text fields named Question plus a number. (Double click on the brush in the ribbon enables multiple copies.)The combo boxes Answer001 to Answer100 (or similar) must also exist in the same form "Audit_Results".
Then you can correct the missaligned conditional format expressions per code:
E.g:Private Sub C_F_Expr_Modify() Dim i As Long, j As Long For i = 2 To 100 With Forms!Audit_Results!Controls("Question" & Format$(i, "000")) For j = 0 To 4 .FormatConditions(j).Modify Type:=acExpression, Operator:=acEqual, Expression1:=Replace(.FormatConditions(j).Expression1, "001", Format$(i, "000")) Next j End With Next i End Sub
- I was going to suggest conditional formatting until I read your final sentence in that paragraph. But on further thought, I think you can make this somewhat less cumbersome by creating the first text box with the appropriate conditional formatting and copy/pasting it for each of the other questions. Of course, that then requires renaming the resulting textboxes.
So, in the end, it's probably going to involve some drudge work.
Unless you are interested in learning how to use Classes in VBA. That is a rather more advanced approach to learn, although it would make this task easier to implement.- Thanks for that George. I have done some VBA on other databases but often had to seek further help when something wouldn't quite work.
- Google is your friend when seeking help. And for specific problems, forums like this are good.
If I were starting over, I'd invest time in learning more about classes because one of the things they can do for you is make tasks like conditional formatting much easier to implement in new forms. That's the power of classes, in fact. Create a class for CFComboBoxes in a single class module and every combo box in that application can use it.
There are many ways to archive this, but conditional formatting is fine.
I assume you have copied all desired formatting from the text field Question001 (or similar) to the other text fields named Question plus a number. (Double click on the brush in the ribbon enables multiple copies.)The combo boxes Answer001 to Answer100 (or similar) must also exist in the same form "Audit_Results".
Then you can correct the missaligned conditional format expressions per code:
E.g:Private Sub C_F_Expr_Modify() Dim i As Long, j As Long For i = 2 To 100 With Forms!Audit_Results!Controls("Question" & Format$(i, "000")) For j = 0 To 4 .FormatConditions(j).Modify Type:=acExpression, Operator:=acEqual, Expression1:=Replace(.FormatConditions(j).Expression1, "001", Format$(i, "000")) Next j End With Next i End Sub
