Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Denver, CO

    Question Unanswered: using comboboxes on a form in excel

    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!

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    You need something like this
    Private Sub UserForm_Activate()
        Dim iRow As Integer
        Dim ListArray() As Variant
        Dim LastRow As Long
        With Sheets("ListOfCharts")
            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



  3. #3
    Join Date
    Jan 2008
    Denver, CO
    Excellent! thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts