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:
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?
Private Sub UserForm_Activate()
Dim iRow As Integer
Dim ListArray() As Variant
Dim LastRow As Long
LastRow = Selection.End(xlUp).Row
ReDim ListArray(LastRow - 1, 1)
For iRow = 1 To LastRow
ListArray(iRow - 1, 0) = .Cells(iRow, 1)
ListArray(iRow - 1, 1) = .Cells(iRow, 2)
cmbSelectDivision.List() = ListArray