conditional formating with VBA onLoad (1 Viewer)

[Wysy]

Hi,
I have a continous form with multiple controls. I would like to have the conditional formating for these controls using VBA on onLoad event.

Private Sub Form_Load()
Dim ctr As Control
Dim frm As Form
Dim fc As FormatCondition

Set frm = Forms(tb)
For Each ctr In frm.Controls
    Set fc = ctr.FormatConditions.Add(acExpression, 0, "ID = txt13", "")
    ctr.FormatCondition.BackColor = vbRed
Next ctr

End Sub

What do i do wrong?
thanks
Andrew

 

[arnelgp]

label control dont have cf.

 

[theDBguy]

Hi. As Arnel was saying, some controls like labels, buttons, checkboxes, etc cannot have conditional formatting. So, you'll have to modify your code to skip those and only CF to Textboxes or Comboboxes.

 

[Wysy]

I see, thank you. I have modified the code, with a select case of ctr. controltype. This part is ok now, but i think i have a problem with the add method.
So what i would like to achieve is that in my continuous form, the row where one of its control has focus, be highlighted and i would like to avoid to create the condition formatting offered in the ribbon for every txtbox in the form.
This means i need to add a conditional formatting with an expression is statement where the control named text13 in the form header equals to the control named ID in the form detail.

 

[arnelgp]

just use CF.
select all textbox you need to highlight.
on CF, "Expression Is", [ID] = [text13]

 

[Wysy]

I modified the code like this:

Private Sub Form_Load()
Dim ctr As Control
Dim frm As Form
Dim fc As FormatCondition

Set frm = Forms(tb)
For Each ctr In frm.Controls
    Select Case ctr.ControlType
    Case acTextBox
    Set fc = ctr.FormatConditions.Add(acExpression, acEqual, "[ID] = [Text13]")
    
    ctr.BackColor = vbGreen

    End Select
    
Next ctr
End Sub

It works but other way around. Every controls backcolor is green but the one where ID=text13 is not

 

[Gasman]

I cannot say why the one is not, but you are setting green for *ANY* textbox when ID matches [text13]?

 

[Wysy]

Yes exactly. OnCurrent text13=ID. then i would like to have the control to be green. In other words highlight it.

 

[Gasman]

Yes exactly. OnCurrent text13=ID. then i would like to have the control to be green. In other words highlight it.

But you are not testing for any particular control, just any textbox?
I no nothing about CF with code, but I've just tried your code on a test form of mine and sure enough it does the same thing?
However you are setting any textbox to green just because it is a textbox? I cannot see that the color change has anything to do with CF formatting?


Walk through the code with F8 and you will see it in action.

Seems you need a few more steps?
https://docs.google.com/document/pub?id=1MNJ15nfDoElcl8liROCqTwLW0iwjLB70zERbh4F1WxE

https://www.access-programmers.co.uk/forums/threads/conditional-formatting-using-vba.301613/

 

[isladogs]

Personally I'd use CF as @arnelgp suggested.
Otherwise try this

Private Sub Form_Load()
Dim ctr As Control
'Dim frm As Form
'Dim fc As FormatCondition

Set frm = Me
For Each ctr In frm.Controls
    Select Case ctr.ControlType
    Case acTextBox
       If ID = Me.Text13 Then
            ctr.BackColor = vbGreen
      Else 'this section may not be needed
           ctr.BackColor = vbWhite
      End if
    End Select
   
Next ctr
End Sub

 

[Gasman]

Arnel suggested this in another thread https://www.access-programmers.co.uk/forums/threads/conditional-formatting-using-vba.301613/

and that code modfied for yours appears to work? Howver it will still apply to every textbox until you change the code.?

HTH

Private Sub Form_Load()
Dim ctr As Control
Dim frm As Form
Dim fc As FormatCondition

Set frm = Forms(Me.Name)
For Each ctr In frm.Controls
    Select Case ctr.ControlType
    Case acTextBox
    Debug.Print ctr.Name & ":" & ctr
    With ctr.FormatConditions
        With .Add(acExpression, acEqual, "[ID] = [Text3]")
    
            .BackColor = vbRed
        End With
    End With
    End Select
    
Next ctr
End Sub

 

[Wysy]

I got it worked! The BackColor part was not right. Instead of setting the control backcolor, the formatcondition backcolor needs to be set.

Private Sub Form_Load()
Dim ctr As Control
Dim frm As Form
Dim fc As FormatCondition

Set frm = Forms(tb)
For Each ctr In frm.Controls
    Select Case ctr.ControlType
    Case acTextBox
    Set fc = ctr.FormatConditions.Add(acExpression, acEqual, "[ID] = [Text13]")
    fc.BackColor = vbRed


    End Select
    
Next ctr
End Sub

this code does exactly what i want: change the color of the whole row if a control of the row has the focus.
thank you all so much for the inputs

 

[Micron]

A select case block makes no sense for this, although it will work. IMO what is better code:

For Each ctr In frm.Controls
If ctr.ControlType = acTextbox Then Set fc.Backcolor = vb red
Next

You don't even need to declare and set a form object - just use Me:
For Each ctr In Me.Controls

 

[Wysy]

great, thanks!

 

[Micron]

Sorry, I copied and pasted part of your code incorrectly:
Set fc = ctr.FormatConditions.Add(acExpression, acEqual, "[ID] = [Text13]")
fc.BackColor = vbRed
so I was thinking

For Each ctr In Me.Controls
  If ctr.ControlType = acTextbox Then
    Set fc = ctr.FormatConditions.Add(acExpression, acEqual, "[ID] = [Text13]")
    fc.BackColor = vbRed
  End If
Next

 

[Wysy]

thank you!

 

[arnelgp]

it's the same thing in post #5 (without VBA).
the problem with the VBA method is if you save the form, it will keep on saving the CF until
you get a runtime error (no more room to add the CF).

 

[Wysy]

I see. Is it correct to add then a delete method?

 

[Wysy]

Is this code sufficient to avoid the runtime error?

Private Sub Form_Close()
For Each ctr In Me.Controls
    
    If ctr.ControlType = acTextBox Then
    ctr.FormatConditions.Delete
    End If

Next ctr


End Sub

 

[isladogs]

Note that whilst you can have up to 50 format conditions using the CF wizard, you can only have 3 format conditions per object using VBA. There is an obscure workaround to that limit but in general there is no advantage using VBA for this purpose.