Transparent Conditional Formatting on Report

[state90]

I have a budget report that has 24 text boxes displaying numeric values. 12 are monthly actual amounts (i.e., JanAct, FebAct, MarAct, etc.) and 12 are budget fields (i.e., JanBud, FebBud, MarBud, etc.). Depending on a pull down menu from a form that displays month [cboDate] (i.e., Jan-2016, Feb-2016, Mar-2016, etc.) the actual field or the budget field for a given month would be transparent. My thought is that is I layered JanAct and JanBud on top of each other and the same with the others (FebAct on top of FebBud and MarAct and MarBud, etc).

I used conditional formatting based on the form selection making JanAct shaded in Green and JanBud background white and the font white but it is not having the desired effect. I realize what I need to do is depending on the form criteria make one transparent and the other visible. How do I do that? The menu driven conditional formatting doesn't include these options that I can see so I imagine I need to use VBA but how? Also, where would I do the VBA coding? Click Alt F11 while the report is open and enter the coding there?

Much appreciation for any help.

 

[CJ_London]

if the property is not available in the conditional formatting window, it won't be available via VBA.

I would use vba to make the control visible or not as the case may be although it is generally not considered a good idea to position one control over another- don't ask me why, I don't know.

The other thing to consider, is to have a single control (for each month) and change the control source via vba

 

[CJ_London]

So thought I would find out why - it is a performance issue - It takes Access more time to render and draw controls that overlap each other than it does non-overlapping controls.