VBA Load Combobox from xls

VBA
Load Combobox from xls

VBA Load combobox from xls and fill a second combobox according to the selection in the first combobox. Note that since we need to know which teacher was chosen in Combobox1 we have to "store" the teacher's ID along with the Name in the first combobox. If your doing this in VB, use the combobox's ItemData property instead. VBA Combos don't have this property so we'll put the ID and the name as two columns in the combobox and make the width of the ID column equal to 0 so that it won't show.

Also, to load more than one column into a combobox you have to set the list property of the combo to an array, and to accommodate for a list with any number of rows we will build a Dynamic Array, and since you can only change the size of the last element of a dynamic array the rows will be the second dimension of the array and the columns will be a constant 2 as the first dimention. We'll then invert the array by transfering it to a second array where the rows are the first dimension because this is how to get the data into the combo as the rows and columns we want.

VBA Load combobox from xls - Part 1

Run this Macro to put a demo list of teachers and students on Sheet 3. If you dont know how to run a macro go here and then come back. After you see how everything works you can hide Sheet 3 from the Format > Sheets > Hide/UnHide menu.

Run the Macro below...

Sub DemoData()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "11"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "22"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "33"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "teacher 1"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "teacher 2"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "teacher 3"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "11"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "33"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "22"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "22"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "33"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "33"
    Range("E7").Select
    ActiveCell.FormulaR1C1 = "11"
    Range("E8").Select
    ActiveCell.FormulaR1C1 = "22"
    Range("E9").Select
    ActiveCell.FormulaR1C1 = "33"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "student 1"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "student 2"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "student 3"
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "student 4"
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "Student 5"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = "student 6"
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "student 7"
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "student 8"
    Range("F9").Select
    ActiveCell.FormulaR1C1 = "Student 9"
End Sub

VBA Load combobox from xls - Part 2

Create a UserForm by Right-clicking on anything in the VBAProject explorer and choosing UserForm.

VBA Load Combobox from xls Add-UserForm

And this code to the Userform by Double-clicking on the form, selecting everything, and then pasting this code...

Dim intRow As Integer
Private Sub ComboBox2_Click()
    Sheets("Sheet1").Range("C" & intRow) = ComboBox2
    intRow = intRow + 1 ' add 1 to get the new row for adding lines
End Sub

Private Sub ComboBox1_Click()
    Dim strValue As String
    Dim intCounter As Integer

    Sheets("Sheet1").Range("A" & intRow) = ComboBox1.Value   ' the teacher's ID
    Sheets("Sheet1").Range("B" & intRow) = ComboBox1.Text
    ComboBox2.Clear
    intCounter = 1
    Do
        strValue = Sheets("Sheet3").Range("F" & intCounter)
        If strValue <> "" Then
            If CStr(Sheets("Sheet3").Range("E" & intCounter)) = ComboBox1.Value Then
                ComboBox2.AddItem strValue
            End If
        intCounter = intCounter + 1
        End If
    Loop Until strValue = ""
End Sub

Private Sub UserForm_Initialize()
    Dim strValue As String
    Dim intCounter As Integer
    Dim arrSup() As Variant, arrSup2() As Variant, arrColumns As Integer
    Dim i As Integer, j As Integer
    arrColumns = 2
    intRowCounter = 1
    ComboBox1.ColumnCount = 2
    ComboBox1.ColumnWidths = "0;150"
    ComboBox1.TextColumn = 2

    Do
        strValue = Sheets("Sheet3").Range("B" & intRowCounter)
        If strValue <> "" Then
            ReDim Preserve arrSup(arrColumns, intRowCounter - 1)
            arrSup(arrColumns - 2, intRowCounter - 1) = Sheets("Sheet3").Range("A" & intRowCounter)
            arrSup(arrColumns - 1, intRowCounter - 1) = strValue
            intRowCounter = intRowCounter + 1
        End If
    Loop Until strValue = ""
    ReDim arrSup2(intRowCounter - 2, arrColumns - 1)
    For i = 0 To arrColumns - 1
        For j = 0 To intRowCounter - 2
            arrSup2(j, i) = arrSup(i, j)
        Next
    Next
    ComboBox1.List() = arrSup2
    intRow = 1
    Do
        If Range("A" & intRow) = "" Then 'find the first blank row.
            Exit Do
        Else
            intRow = intRow + 1
        End If
    Loop
End Sub

VBA Load combobox from xls - Part 3

For now we will open the UserForm from within the IDE (VBA Editor). To run the form when you open Excel, Double ckick on ThisWorkbook in the VBAProject explorer and add this...

Private Sub Workbook_Open()
UserForm1.Show
End Sub

VBA Load combobox from xls - Tip 1

While testing, you can run the form by double-clicking on it to open the Code Pane (View Code) and then pressing F5 or clicking Run (the green triangle icon).

VBA Load combobox from xls - Tip 2

VBA Load Combobox from xls - Set Breakpoint

You can also set Breakpoints on any rows by pressing F9 or clicing in the left grey margin. Code execution will stop at a Breakpoint and you can continue from there line by line with F8, stop the code by clicking the square to the right of the run button, or Run the code to completion or the next Breakpoint with F5.

End of the VBA Load combobox from xls Tutorial

VBALoad Combobox from xls

VBA Load combobox from xls - Part 1

VBA Load combobox from xls - Part 2

VBA Load combobox from xls - Part 3

VBA
Load Combobox from xls