Coloration de plusieurs TEXTBOX à la fois

Bonjour,

J'arrive à modifier la couleur de mes TEXTBOX une par une.

TextBox54.Value = Format(Sheets("Calculs").Range("C11"), "0.0 %")

Private Sub TextBox54_Change()
Select Case Left(TextBox54.Value, Len(TextBox54.Value) - 1)
        Case -1000000 To -0.0001
            TextBox54.BackColor = RGB(180, 0, 0)
            TextBox54.ForeColor = vbWhite
        Case 0.0001 To 1000000
            TextBox54.BackColor = vbGreen
            TextBox54.ForeColor = vbBlack
        Case Is = 0
            TextBox54.BackColor = vbGrey
            TextBox54.ForeColor = vbWhite
    End Select
End Sub

J'aimerai pouvoir modifier plusieurs TEXTBOX à la fois, mais je m'y prends mal.

Private Sub Change_Color()
For i = 37 To 48
    Case TextBoxi.Value > 0

            TextBoxi.BackColor = vbGreen
            TextBoxi.ForeColor = vbWhite

    Case TextBoxi.Value < 0
            TextBoxi.BackColor = RGB(180, 0, 0)
            TextBoxi.ForeColor = vbBlack
    Else
            TextBoxi.BackColor = vbGrey
            TextBoxi.ForeColor = vbWhite
    End With

Next
End Sub

Merci d'avance.

Bonjour Olivier TP

au lieu de

for i = to

. Textboxi....

Faire

for each ctrl in me.Controls

if typeof ctrl is textbox then

if ctrl.value>0 then

...

Bonjour,

A+

Bonjour…

Sans module de classe : un exemple à adapter (cas et couleurs)

Sub Change_Color() 'attention à l'appel
  Dim i As Byte, Ct As Control
  For i = 37 To 48
    Set Ct = Me("Textbox" & i)
    Select Case Ct.Value
      Case "": Ct.BackColor = vbBlack: Ct.ForeColor = vbWhite
      Case Is > 0: Ct.BackColor = vbGreen: Ct.ForeColor = vbBlack
      Case Else: Ct.BackColor = vbRed: Ct.ForeColor = vbBlack
    End Select
  Next
End Sub