VBA: code simplification for textbox in Worksheets

Question

Main goal: The Value of each Textbox depends on different cells value in database Previously i used these codes to implement same function to all the textboxes in an Userform :

Private Sub UserForm_Initialize()


Dim cntr As Integer

Dim cntr2 As Integer
Dim z As Integer
Dim cntr3 As Integer




cntr3 = Application.WorksheetFunction.CountA(Range("C:C"))
cntr2 = Application.WorksheetFunction.CountA(Range("A17:BL17"))
cntr = Application.WorksheetFunction.CountA(Range("A19:BL19"))

For i = 5 To cntr
For y = 20 To cntr3
If Cells(17, i).Value = "DC Test" And Worksheets(1).cbDevice_List.Value = Cells(y, 3).Value Then


For z = i To cntr
For x = 1 To 17

If Controls("tb" & x).Value = "" Then


Select Case Cells(y, z).Interior.ColorIndex
   Case 3: Controls("tb" & x).ForeColor = RGB(255, 0, 0)
   Controls("tb" & x).Value = "Failed"
    Case 4: Controls("tb" & x).ForeColor = RGB(0, 255, 0)
    Controls("tb" & x).Value = "Passed"
    Case Else: Controls("tb" & x).Value = "N/A"
   End Select
    z = z + 1
End If
Next x
Next z

End If

Next y

Next i


End Sub

then, this time i intend to use the same structure of codes also to 20 textboxes which located in a Worksheet instead in an Userform. So the syntax "controls" cannot be used outside from userform. So i took the longest way :

Private Sub CommandButton1_Click()



Dim cntr As Integer
Dim cntr2 As Integer
Dim z As Integer


cntr = Range("C23", Range("C23").End(xlDown)).Count
cntr2 = Application.WorksheetFunction.CountA(Range("D22:X22"))

For i = 0 To cntr

If Me.list_device.Value = Range("C23").Offset(i, 0).Value Then

For z = 0 To cntr2

'For y = 1 To 20




    'Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    'Case 3: Worksheets(2).Controls("TextBox" & y).ForeColor = RGB(255, 0, 0)
    'Worksheets(2).Controls("TextBox" & y).Value = "Failed"
    'Case 4: Controls("TextBox" & y).ForeColor = RGB(0, 255, 0)
    'Controls("TextBox" & y).Value = "Passed"
    'Case Else: Controls("TextBox" & y).Value = "Wrong Handler"
    'End Select
    'z = z + 1
    
    
   
    
If z = 1 Then

    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox1.ForeColor = rgb(255, 0, 0)
    TextBox1.Value = "Failed"
    Case 4: TextBox1.ForeColor = rgb(0, 255, 0)
    TextBox1.Value = "Passed"
    Case Else: TextBox1.ForeColor = rgb(0, 0, 0)
    TextBox1.Value = "Wrong Handler"
    End Select
    
End If

If z = 2 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox2.ForeColor = rgb(255, 0, 0)
    TextBox2.Value = "Failed"
    Case 4: TextBox2.ForeColor = rgb(0, 255, 0)
    TextBox2.Value = "Passed"
    Case Else: TextBox2.ForeColor = rgb(0, 0, 0)
    TextBox2.Value = "Wrong Handler"
    End Select
    
End If
If z = 3 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox3.ForeColor = rgb(255, 0, 0)
    TextBox3.Value = "Failed"
    Case 4: TextBox3.ForeColor = rgb(0, 255, 0)
    TextBox3.Value = "Passed"
    Case Else: TextBox3.ForeColor = rgb(0, 0, 0)
    TextBox3.Value = "Wrong Handler"
    End Select
    
End If
If z = 4 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox4.ForeColor = rgb(255, 0, 0)
    TextBox4.Value = "Failed"
    Case 4: TextBox4.ForeColor = rgb(0, 255, 0)
    TextBox4.Value = "Passed"
    Case Else: TextBox4.ForeColor = rgb(0, 0, 0)
    TextBox4.Value = "Wrong Handler"
    End Select
    
End If
If z = 5 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox5.ForeColor = rgb(255, 0, 0)
    TextBox5.Value = "Failed"
    Case 4: TextBox5.ForeColor = rgb(0, 255, 0)
    TextBox5.Value = "Passed"
    Case Else: TextBox5.ForeColor = rgb(0, 0, 0)
    TextBox5.Value = "Wrong Handler"
    End Select
    
End If
If z = 6 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox6.ForeColor = rgb(255, 0, 0)
    TextBox6.Value = "Failed"
    Case 4: TextBox6.ForeColor = rgb(0, 255, 0)
    TextBox6.Value = "Passed"
    Case Else: TextBox6.ForeColor = rgb(0, 0, 0)
    TextBox6.Value = "Wrong Handler"
    End Select
    
End If
If z = 7 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox7.ForeColor = rgb(255, 0, 0)
    TextBox7.Value = "Failed"
    Case 4: TextBox7.ForeColor = rgb(0, 255, 0)
    TextBox7.Value = "Passed"
    Case Else: TextBox7.ForeColor = rgb(0, 0, 0)
    TextBox7.Value = "Wrong Handler"
    End Select
    
End If
If z = 8 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox8.ForeColor = rgb(255, 0, 0)
    TextBox8.Value = "Failed"
    Case 4: TextBox8.ForeColor = rgb(0, 255, 0)
    TextBox8.Value = "Passed"
    Case Else: TextBox8.ForeColor = rgb(0, 0, 0)
    TextBox8.Value = "Wrong Handler"
    End Select
    
End If
If z = 9 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox9.ForeColor = rgb(255, 0, 0)
    TextBox9.Value = "Failed"
    Case 4: TextBox9.ForeColor = rgb(0, 255, 0)
    TextBox9.Value = "Passed"
    Case Else: TextBox9.ForeColor = rgb(0, 0, 0)
    TextBox9.Value = "Wrong Handler"
    End Select
    
End If
If z = 10 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox10.ForeColor = rgb(255, 0, 0)
    TextBox10.Value = "Failed"
    Case 4: TextBox10.ForeColor = rgb(0, 255, 0)
    TextBox10.Value = "Passed"
    Case Else: TextBox10.ForeColor = rgb(0, 0, 0)
    TextBox10.Value = "Wrong Handler"
    End Select
    
End If
If z = 11 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox11.ForeColor = rgb(255, 0, 0)
    TextBox11.Value = "Failed"
    Case 4: TextBox11.ForeColor = rgb(0, 255, 0)
    TextBox11.Value = "Passed"
    Case Else: TextBox11.ForeColor = rgb(0, 0, 0)
    TextBox11.Value = "Wrong Handler"
    End Select
    
End If
If z = 12 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox12.ForeColor = rgb(255, 0, 0)
    TextBox12.Value = "Failed"
    Case 4: TextBox12.ForeColor = rgb(0, 255, 0)
    TextBox12.Value = "Passed"
    Case Else: TextBox12.ForeColor = rgb(0, 0, 0)
    TextBox12.Value = "Wrong Handler"
    End Select
    
End If
If z = 13 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox13.ForeColor = rgb(255, 0, 0)
    TextBox13.Value = "Failed"
    Case 4: TextBox13.ForeColor = rgb(0, 255, 0)
    TextBox13.Value = "Passed"
    Case Else: TextBox13.ForeColor = rgb(0, 0, 0)
    TextBox13.Value = "Wrong Handler"
    End Select
    
End If
If z = 14 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox14.ForeColor = rgb(255, 0, 0)
    TextBox14.Value = "Failed"
    Case 4: TextBox14.ForeColor = rgb(0, 255, 0)
    TextBox14.Value = "Passed"
    Case Else: TextBox14.ForeColor = rgb(0, 0, 0)
    TextBox14.Value = "Wrong Handler"
    End Select
    
End If
If z = 15 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox15.ForeColor = rgb(255, 0, 0)
    TextBox15.Value = "Failed"
    Case 4: TextBox15.ForeColor = rgb(0, 255, 0)
    TextBox15.Value = "Passed"
    Case Else: TextBox15.ForeColor = rgb(0, 0, 0)
    TextBox15.Value = "Wrong Handler"
    End Select
    
End If
If z = 16 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox16.ForeColor = rgb(255, 0, 0)
    TextBox16.Value = "Failed"
    Case 4: TextBox16.ForeColor = rgb(0, 255, 0)
    TextBox16.Value = "Passed"
    Case Else: TextBox16.ForeColor = rgb(0, 0, 0)
    TextBox16.Value = "Wrong Handler"
    End Select
    
End If
If z = 17 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox17.ForeColor = rgb(255, 0, 0)
    TextBox17.Value = "Failed"
    Case 4: TextBox17.ForeColor = rgb(0, 255, 0)
    TextBox17.Value = "Passed"
    Case Else: TextBox17.ForeColor = rgb(0, 0, 0)
    TextBox17.Value = "Wrong Handler"
    End Select
    
End If
If z = 18 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox18.ForeColor = rgb(255, 0, 0)
    TextBox18.Value = "Failed"
    Case 4: TextBox18.ForeColor = rgb(0, 255, 0)
    TextBox18.Value = "Passed"
    Case Else: TextBox18.ForeColor = rgb(0, 0, 0)
    TextBox18.Value = "Wrong Handler"
    End Select
    
End If
If z = 19 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox19.ForeColor = rgb(255, 0, 0)
    TextBox19.Value = "Failed"
    Case 4: TextBox19.ForeColor = rgb(0, 255, 0)
    TextBox19.Value = "Passed"
    Case Else: TextBox19.ForeColor = rgb(0, 0, 0)
    TextBox19.Value = "Wrong Handler"
    End Select
    
End If
If z = 20 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox20.ForeColor = rgb(255, 0, 0)
    TextBox20.Value = "Failed"
    Case 4: TextBox20.ForeColor = rgb(0, 255, 0)
    TextBox20.Value = "Passed"
    Case Else: TextBox20.ForeColor = rgb(0, 0, 0)
    TextBox20.Value = "Wrong Handler"
    End Select
    
End If

If z = 21 Then
    Select Case Range("C23").Offset(i, z).Interior.ColorIndex
    Case 3: TextBox21.ForeColor = rgb(255, 0, 0)
    TextBox21.Value = "Failed"
    Case 4: TextBox21.ForeColor = rgb(0, 255, 0)
    TextBox21.Value = "Passed"
    Case Else: TextBox21.ForeColor = rgb(0, 0, 0)
    TextBox21.Value = "Wrong Handler"
    End Select
    
End If
'Next y
Next z

End If


Next i

End Sub

So is there any sorts of simplification that i can implement for a larger number of TextBox? I've read about the syntax "oleObject" but couldnt find a way to how to implement it. Thanks in advance. Your helps and advices are really much appreciated!

TinMan · Answer 1 · 2022-11-30T06:27:47.043

An OLEObject is a wrapper an ActiveX control. The actual control can be referenced using the OLEObject.Object property.

For example:

Set Box = Worksheets(2).OLEObjects("TextBox" & z + 1).Object

Dim Box As MSForms.TextBox

For i = 0 To cntr

    If Me.list_device.Value = Range("C23").Offset(i, 0).Value Then

        For Z = 0 To cntr2
            Set Box = Worksheets(2).OLEObjects("TextBox" & Z + 1).Object
        
            Select Case Range("C23").Offset(i, Z).Interior.ColorIndex
                Case 3:
                    Box.ForeColor = RGB(255, 0, 0)
                    Box.Value = "Failed"
                Case 4:
                    Box.ForeColor = RGB(0, 255, 0)
                    Box.Value = "Passed"
                Case Else:
                    Box.ForeColor = RGB(0, 0, 0)
                    Box.Value = "Wrong Handler"
            End Select

        Next Z

    End If

Next i

so how do i access the property of the BOX, ex. ForeColor or Value, like the same way i control the number of "tb" in Controls("tb" & x).Value ? is it Box.Value = ""? — taqiuddin yusri, Nov 30 '22 at 06:17

VBA: code simplification for textbox in Worksheets

1 Answers1