I'm struggling to build my first useful document with VBA and mostly I've been able to plug away until I get what I want to happen to work, but I've run into a wall.
I have a sheet with information in "B" column and information in "i" column that I would like to make into ComboBox line-items in my UserForm.
Essentially, column "B" contains tasks, and Column "i" contains due dates. When the User opens the drop down of my ComboBox, I want each line to display the task name and the due date, so tasks that are repeated won't be confused.
I have tried to do this half a dozen ways, and I have seen error after error.
I suspect I need to build an array, or a listobject, or something, but when I tried that, everything remained broken.
Here is the non-functional sub I first attempted. Can someone tell me how to do what I'm dreaming of?
`Private Sub UserForm_Initialize()
Dim task As String 'representing each cell in my "B" column, which contains an entry
Dim due As String 'representing each cell in my "i" column, which contains an entry
Dim NumElements As Integer 'representing number of rows populated in my document
Dim ws As Worksheet 'for referencing specific sheet
NumElements = Sheets("Engine").Range("$B$2") 'Yields number of entries using a "=COUNTA" formula in the document.
'I would love to have the number of entries calculated within vba, instead of in the document,
'but I haven't been able to figure that out.
Set ws = Sheets("Tasks") 'the sheet with my database in it
'attempting to pull data from each cell in "B" column to populate first half of each line in ComboBox "Combo_Task_Select"
For Each task In ws.Range("$B$8:$B$" & NumElements)
Me.Combo_Task_Select.AddItem task.Value 'populate with data from variable "task"
'attempting to pull data from each cell in "i" column to populate second half of each line in ComboBox "Combo_Task_Select"
For Each due In ws.Range("$I$8:$I$" & NumElements)
Me.Combo_Task_Select.AddItem due.Value 'populate with data from variable "due"
Next due 'attempt at keeping the looping "due" and "task" variables in synch, by nestling "next"s
Next task
End Sub`
NumElements = range(range("b8"), range("b8").End(xlDown)).Rows.Count gives you the number of elements in the column, beginning from B8 until above the first empty cell.
For the dropdown you can opt for a singlecolumn combobox or a multiple (2) column combobox. I suggest the 2 column version, it is not much more complicated than the single column. You can populate the combox in 2 steps: fill the values to an array and then load the array to the combobox. (The solution you chose is fine either, yet I prefer this way because it is easier to debug.)
2 column version:
Dim arr(), i As Long
Redim arr(1 to NumElements,1 to 2)
For i=1 to NumElements
arr(i,1)=cells(7+i,2) ' column B
arr(i,2)=cells(7+i,9) ' column I
Next
Me.Combo_Task_Select = arr
Finished. Remember setting the column count property in the form designer. Or you can set it dynamically with Me.Combo_Task_Select.ColumnCount=2
Single column version: you need to make a single string value from the 2 cells, like this:
Redim arr(1 to NumElements)
For i=1 to NumElements
arr(i)=Left(cells(7+i,2) & worksheetFunction.rept(" ",10), 10) & cstr(cells(7+i,9))
next
Adjust "10" according to the max length of tasks.