Excel Forum

Code: Select all

PPrivate Sub ComboBox1_Change()
    Dim myRange As Range, r As Range
    Dim arrItem(9) As Variant, arrQ(9) As Variant
    Dim arrUnit(9) As Variant, arrPrice(9) As Variant
    Dim i As Integer, j As Integer
    arrItem(0) = "TextBox2": arrQ(0) = "TextBox3": arrUnit(0) = "TextBox4": arrPrice(0) = "TextBox5"
    arrItem(1) = "TextBox6": arrQ(1) = "TextBox7": arrUnit(1) = "TextBox8": arrPrice(1) = "TextBox9"
    arrItem(2) = "TextBox10": arrQ(2) = "TextBox11": arrUnit(2) = "TextBox12": arrPrice(2) = "TextBox13"
    arrItem(3) = "TextBox14": arrQ(3) = "TextBox15": arrUnit(3) = "TextBox16": arrPrice(3) = "TextBox17"
    arrItem(4) = "TextBox18": arrQ(4) = "TextBox19": arrUnit(4) = "TextBox20": arrPrice(4) = "TextBox21"
    arrItem(5) = "TextBox22": arrQ(5) = "TextBox23": arrUnit(5) = "TextBox24": arrPrice(5) = "TextBox25"
    arrItem(6) = "TextBox26": arrQ(6) = "TextBox27": arrUnit(6) = "TextBox28": arrPrice(6) = "TextBox29"
    arrItem(7) = "TextBox30": arrQ(7) = "TextBox31": arrUnit(7) = "TextBox32": arrPrice(7) = "TextBox33"
    arrItem(8) = "TextBox34": arrQ(8) = "TextBox35": arrUnit(8) = "TextBox36": arrPrice(8) = "TextBox37"
    arrItem(9) = "TextBox38": arrQ(9) = "TextBox39": arrUnit(9) = "TextBox40": arrPrice(9) = "TextBox41"
    With Sheets("Alldata")
        Set myRange = .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
    End With
    For j = 0 To 9
        Me.Controls(arrItem(j)).Text = ""
        Me.Controls(arrQ(j)).Text = ""
        Me.Controls(arrUnit(j)).Text = ""
        Me.Controls(arrPrice(j)).Text = ""
    Next j
    For Each r In myRange
        If CStr(r.Value) = Me.ComboBox1.Text Then
            Me.Controls(arrItem(i)).Text = r.Value
            Me.Controls(arrQ(i)).Text = r.Offset(0, 1).Value
            Me.Controls(arrUnit(i)).Text = r.Offset(0, 2).Value
            Me.Controls(arrPrice(i)).Text = r.Offset(0, 3).Value
            i = i + 1
            If i > 9 Then Exit For
        End If
    Next r
End Sub