change color of TextBox background on userform if 0

[ipbr21054]

Hi,
This works for me but need some help please to allow it to work for a range as opposed to the current 1 TextBox.

On my userform i have 26 textboxes & the code should work like so.
If TextBox1 - TextBox26 has a value of 0 Then
The TextBox background color should be vbRed
If the value is greater than 0 Then
The TextBox background should be vbWhite

If Me.TextBox1.Value = "0" Then
Me.TextBox1.BackColor = vbRed
Else
Me.TextBox1.BackColor = vbWhite

Thanks

 

[Micron]

maybe like in post 2 here?

Userform Textbox conditional formatting

Hello, Im trying to get the text box to go red if the value is over 318 and green if under. This code works if I put the value directly into the code. Private Sub TextBox24_Change() If Textbox3.Text = "Cathode Mother Roll" And TextBox24.Value > "318" Then TextBox24.BackColor = RGB(255, 0, 0)...

www.mrexcel.com

Just make sure which type of data your cell(s) contains - text or numbers. You might need to drop the quotes - try just 0 not "0". I don't understand the comment (at that link) re textboxes containing text - they should be able to accept any data type in my opinion, but then I'm more experienced in Access.

 

[ipbr21054]

Cells ? It’s a userform.
I mentioned 0 in textbox as only numbers are used. So I wasn’t sure if it made any difference so I mentioned it just in case.

 

[Micron]

You did say

need some help please to allow it to work for a range

and a range means at least 1 cell? Maybe you meant a group or collection of textboxes. If what you have works, then what are you after? I'm guessing it's a way to do this for 26 textboxes without repeating that code 26 times? Maybe something like

Dim ctl As Control
For Each ctl In UserForm1.Controls
   If TypeName(ctl) = "Textbox" Then
      If ctl = 0 Then
          ctl.BackColor = vbRed
      Else
          ctl.BackColor = vbWhite
      End If
   End If
Next

Unfortunately I don't really know what event is best for triggering that for your case. Perhaps form click event, or add a button to call the code and apply the formatting accordingly.

 

[ipbr21054]

Hi
Range as in a range of text boxes as opposed to just textbox1.

My code works for 1 textbox but I wasn’t sure how to write it for a range of textboxes hence Textbox1 - Textbox26
I will try your code tomorrow.

Thanks.

 

[ipbr21054]

Here is the code for the form.
When i include your code nothing happens.
In that i mean no error & no TextBoxes with a value of 0 turn Red ?

Private Sub UserForm_Initialize()
Dim ctl As Control
Me.TextBox100.SetFocus

Set ws = ActiveSheet
Me.TextBox1 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "ACCORD ID 48")
Me.TextBox2 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "ACCORD ID 8E")
Me.TextBox3 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "BLACK NRK ID 46")
Me.TextBox4 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "BLACK NRK ID 48")
Me.TextBox5 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "BLACK NRK ID 8E")
Me.TextBox6 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "CAT 1 REMOTE")
Me.TextBox7 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "CIVIC CE0523")
Me.TextBox8 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "CRV HLIK-1T")
Me.TextBox9 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "CRV ID 48")
Me.TextBox10 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "FLIP HLIK-1T 2B")
Me.TextBox11 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "FLIP HLIK-1T 3B")
Me.TextBox12 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "FRV ID 48")
Me.TextBox13 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "FRV ID 8E")
Me.TextBox14 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "G8D-345H-A")
Me.TextBox15 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "G8D-348H-A")
Me.TextBox16 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "G8D-350H-A")
Me.TextBox17 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "G8D-453H-A")
Me.TextBox18 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "G8D-456H-A")
Me.TextBox19 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "HON 58 ID 13")
Me.TextBox20 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "HON 58 ID 48")
Me.TextBox21 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "JAZZ HLIK-1T")
Me.TextBox22 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "JAZZ ID 48")
Me.TextBox23 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "JAZZ ID 8E")
Me.TextBox24 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "S2000 AIO ID 48")
Me.TextBox25 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "72147-S2H-G01")
Me.TextBox26 = Application.WorksheetFunction.CountIf(ws.Range("$F$21:$F$2003"), "72147-S2H-G02")


For Each ctl In HondaSheetSoldItemCount.Controls
   If TypeName(ctl) = "Textbox" Then
      If ctl = 0 Then
          ctl.BackColor = vbRed
      Else
          ctl.BackColor = vbWhite
      End If
   End If
Next


End Sub

 

[dmt32]

hi,
untested but see if this update to your code does what you want

Private Sub UserForm_Initialize()
    Dim i       As Long
    Dim strText As String
    
    Me.TextBox100.SetFocus
    
    Set ws = ActiveSheet
    For i = 1 To 26
     strText = Choose(i, "ACCORD ID 48", "ACCORD ID 8E", "BLACK NRK ID 46", "BLACK NRK ID 48", "BLACK NRK ID 8E", _
                        "CAT 1 REMOTE", "CIVIC CE0523", "CRV HLIK-1T", "CRV ID 48", "FLIP HLIK-1T 2B", _
                        "FLIP HLIK-1T 3B", "FRV ID 48", "FRV ID 8E", "G8D-345H-A", "G8D-348H-A", _
                        "G8D-350H-A", "G8D-453H-A", "G8D-456H-A", "HON 58 ID 13", "HON 58 ID 48", _
                        "JAZZ HLIK-1T", "JAZZ ID 48", "JAZZ ID 8E", "S2000 AIO ID 48", "72147-S2H-G01", _
                        "72147-S2H-G02")
                        
     With Me.Controls("TextBox" & i)
        .Value = Application.CountIf(ws.Range("$F$21:$F$2003"), strText)
        .BackColor = IIf(.Value = 0, rgbRed, rgbWhite)
    End With
    
    Next i


End Sub

Dave

 

[Micron]

You'll have to excuse my lack of experience with Excel userforms as I'm primarily an Access user trying to help out here. TBH, I find Excel userforms to be quite clunky and limited in comparison, and can't help thinking how much better Access is at stuff that people are trying to do in Excel.

Before I posted I researched the controltype name for a textbox but honestly, didn't test my code. For that I apologize as I've discovered (re-discovered actually) how finicky Excel is with character case. Change it to TextBox and it might work.

 

[ipbr21054]

This worked for me in the end.
Thanks to all that replied.

For Each tbox In Me.Controls
    If TypeName(tbox) = "TextBox" Then
        If tbox.Value = "0" Then tbox.Value = "ERROR"
        If tbox.Value = "ERROR" Then tbox.BackColor = vbRed
    End If
Next tbox