using comboboxes on a form in excel

ArielZusya · #1 (**permalink**) 01-12-08, 18:59

I'm new to excel programming (I've recently gotten my feet wet in Access programming and am having a great time with that) but I have a project that I need to implement in excel. I'd like to use a form called on load of the file that has a combo box on it. I'd like the combobox to populate from a couple of columns on a worksheet in my file. The worksheet has no header. In Column A is simply numbers 1-7 and in Column B is a list of courtrooms and the type of case (Civil or Criminal). In otherwords it looks something like this:

1 Division 201 Criminal
2 Division 202 Civil
3 Division 206 Criminal
4 Division 401 Criminal
5 Division 401 Civil
6 Division 407 Criminal
7 Division 408 Criminal

I'd like the user to only see Column B in the combobox but my intention with column A was to create a sort of key number (like I said... just coming off an access programming binge) as I was thinking it would be easier to reference it later in code. Two questions:

1) Am I thinking about this the right way?

2) How do I implement this? My combobox is called cmbSelectDivision and my worksheet is called ListOfCharts. What should this code look like? Should it be in the UserForm_Active or cmbSelectDivision_Change or something else?

Thanks for your help!

MikeTheBike · #2 (**permalink**) 01-14-08, 08:54

Hi

You need something like this

Code:

Private Sub UserForm_Activate()
    Dim iRow As Integer
    Dim ListArray() As Variant
    Dim LastRow As Long
    
    With Sheets("ListOfCharts")
        .Range("A65536").Select
        LastRow = Selection.End(xlUp).Row
        .Cells(1, 1).Select
        ReDim ListArray(LastRow - 1, 1)
    
        For iRow = 1 To LastRow
            ListArray(iRow - 1, 0) = .Cells(iRow, 1)
            ListArray(iRow - 1, 1) = .Cells(iRow, 2)
        Next iRow
    End With
    
    cmbSelectDivision.List() = ListArray
End Sub

??

MTB

ArielZusya · #3 (**permalink**) 01-14-08, 17:00

Excellent! thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech