Add Items Into Combobox In Alphabetic Order

Excel VBA Automatically Fill Text Boxes Based On A Selection Combobox 

        When userform is opened ,Combobox on the userform is filled with values in Column B alphabatically. Our codes that we used to fill combobox as alphabatically  :
Sub fill_combobox()
Dim LastRow, a, b As Long, c As Variant
LastRow = Sheets("S1").Cells(Rows.Count, 2).End(xlUp).Row

For x = 2 To LastRow
ComboBox1.AddItem Cells(x, 2).Value
  For a = 0 To ComboBox1.ListCount - 1
  For b = a To ComboBox1.ListCount - 1
        If ComboBox1.List(b) < ComboBox1.List(a) Then
c = ComboBox1.List(a)
    ComboBox1.List(a) = ComboBox1.List(b)
    ComboBox1.List(b) = c
       End If
End Sub

       Data are filled into text boxes based on selection the combobox. With that loop in ComboBox1_Change() procedure:
For Each bul In Sheets("S1").Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
    If bul = ComboBox1 Then
    i = 0
        TextBox1 = bul.Offset(i, 1)
        TextBox2 = bul.Offset(i, 2)
        TextBox4 = bul.Offset(i, 3)
        TextBox5 = bul.Offset(i, 4)
        TextBox6 = bul.Offset(i, 5)
        TextBox7 = bul.Offset(i, 6)
        TextBox8 = bul.Offset(i, 7)
     End If
    Next bul

Also,combobox is sorted in alphabetical order when new data is added and  deleted.

The id numbers (1-2-3-4 etc.) are automatically added to Column A. Id numbers are generated automatically again  when new record is added to the worksheet and  when record is removed from combobox.

No comments:

Post a Comment