Vba-formatcondition

[diegomarino]

hi, when i click on a textbox in a continuos subform i'd like to add a conditional formatting, so the backcolor turn green the first time, and when i click again the same textbox the backcolor should return to the normal backcolor.

i think i can do that by adding a formatcondition

Set Frmt = Forms![Amministrazione]![OverviewFinanziario].Scheda![SpeseTotali].Moneymap.FormatConditions.Add(acFieldValue, acEqual, Me.Moneymap)

that's the code, it gives me runtime error 438
thanks

[Welshgasman]

I try to keep things simple.
Why not just use the click event?

Private Sub Text1_Click()
If Me.Text1.BackColor = vbWhite Then
    Me.Text1.BackColor = vbGreen

Else
    Me.Text1.BackColor = vbWhite
End If
End Sub

Sorry, just realised that would change all specific controls

[diegomarino]

yea, this would have been a perfect world

[Welshgasman]

I tried with CF and same effect?

[Micron]

If you can live with using CF where condition is =Field Has Focus then easy enough as long as you're willing to click to a different record or field in the record that has no CF.

EDIT - I guess it doesn't matter if any other field has cf or not. The one that has the focus condition will lose the focus, thus will revert.
If that fields backcolor changes in all records, then your field is not bound. In that case, you're beat (AFAIK).

[diegomarino]

YES WE CAN!

Function ConditionalFrmt(TB As TextBox) ' per applicare formattazione condizionale modificato da meDim objFormatConds As FormatCondition
Dim i As Integer 'index number for specific format conditions


i = 0


'clear out just in case FormatConditions accidentially got saved
'with the form at some point.
TB.FormatConditions.Delete


Set objFormatConds = TB.FormatConditions.Add(acFieldValue, acLessThan, -500)
'add formatting for the condition just created.
With TB.FormatConditions(i)
    .BackColor = RGB(0, 255, 0)
End With
i = i + 1


End Function

I've just some problems with text, but with numbers works!!

[diegomarino]

actually, i can create the CF but it's not applied on text textbox, i have to open the CF window and apply the rule i created on vba



ps i just realized that it seems not working on currency field, instead it works and the green backcolor is applied only to the textboxes less than 500

[diegomarino]

Found it!!!

Set objFormatConds = TB.FormatConditions.Add(acFieldValue, acLessThan, """soggiorno""" )

you have to use """ for text not just "!!
thanks very much (expecially to me )

[diegomarino]

Function ConditionalFrmt(TB As TextBox, Arr() As String) ' per applicare formattazione condizionale modificato da me   Dim objFormatConds As FormatCondition
   Dim i As Integer, Counter As Long 'index number for specific format conditions
   i = 0
   Counter = 0




   'clear out just in case FormatConditions accidentially got saved
   'with the form at some point.
   TB.FormatConditions.Delete
   For Counter = LBound(Arr) To UBound(Arr)
      Debug.Print Arr(Counter)
      Set objFormatConds = TB.FormatConditions.Add(acFieldValue, acEqual, """" & Arr(Counter) & """")
      'add formatting for the condition just created.
      With TB.FormatConditions(i)
          .BackColor = RGB(0, 255, 0)
      End With
      i = i + 1
   Next
'add formatting for the condition just created.


End Function