Cannot populate combo box with array

[groc426]

Hello everyone-
I've been learning a lot from this forum about VBA, but while building my own project, I've run into a problem that I cannot figure out, despite hours of searching. But...I'm learning

Attempting to do:
Read down a column in excel and store the information in an array. Take this array and populate a combo box in a form. Simple, but I'm having difficulties.

Code:
Private Sub acctBox1_Change()
addTransaction
End Sub


Function addTransaction()
Dim curRow As Integer
Dim accountArr() As String
Dim x As Integer
Dim a As String

x = 1
curRow = 4
ReDim accountArr(1 To 100)

Do Until Sheets("Trial Balance").Cells(curRow, 2) = Empty
For x = x To curRow
curRow = curRow + 1
accountArr(x) = Sheets("Trial Balance").Cells(curRow, 2).Value
Next x
Loop
ReDim Preserve accountArr(1 To x)
transactionEntry.acctBox1.List = accountArr
End Function

 

[DCrake]

Have you set the rowsource type to Value List?

 

[LPurvis]

Looks to me like you're using a UserForm control syntax of directly assigning an array to a listcontrol.
Is this form that you're using in Access or in Excel itself (or is a UserForm in Access even? Which isn't very likely I grant you...).

As mentioned by DCrake, if this is an Access form then you'll need to assign your data differently. (It'll be easy enough to "Join" your array into a delimited string).

Cheers.

 

[groc426]

DCrake-
Yep attempted to set the row source type to value list assuming the syntax is as such:
.RowSourceType = "Value List"
I'm using Excel, is this a valid with excel?

LPurvis-
Yes, I am using a UserForm control to directly assign an array to a listcontrol, within Excel

Any suggestions?
Thanks for the responses thus far!

 

[LPurvis]

Well - this is the Microsoft Access Discussion > Modules & VBA forum - so David's assumption that this was an Access form was pretty reasonable.
I believe there is an Excel forum here. (Though it probably doesn't get a lot of traffic).

Anyway... I'm afraid
"..but I'm having difficulties"
really doesn't tell us much either.
What are you encountering exactly. An error? (And before you reply with "yes" - what error? ;-)
There's no penalty for being very descriptive in your questions. It would be somewhat hypocritical of me were I to be critical of that verbose practice. lol

 

[groc426]

LPurvis-
Whoops...I apologize for posting in the wrong forum.

I'm not receiving any type of error. When I step through the vba code, everything executes fine. Each item in the column (in Excel) is assigned to an index number (x) in the array. But when I go to the UserForm combo box that is to be populated by this array (accountArr), nothing shows up. Just a blank, empty combo box So I can't figure out how to apply this array so that it populates my combo box.

Any other info and just ask. I hope that was descriptive enough. Thank you so much for the help! Again I'm sorry about this being posted in the wrong forum.

 

[DCrake]

If it feasable can you attach a copy of the xls to look at?

 

[LPurvis]

Agreed. Sorry - been to busy to even give this a second thought. (Or a first one for that matter).
Having the file will save any flapping around and time wasting on our part.

 

[chergh]

To add values to a combox on a userform in excel it is doen like this:

for i = lbound(accountArr) to ubound(accountArr)

   combobox1.additem accountArr(i)

next i

 

[Brianwarnock]

LPurvis-
Whoops...I apologize for posting in the wrong forum.

Again I'm sorry about this being posted in the wrong forum.

It is not unusual for Excel VBA questions to be posted here, and you did say in your 1st post

Read down a column in excel

So no need to grovel

Brian

 

[LPurvis]

Very quickly (due to being so short on time) looking at your code...
I notice the array you're building is 1 dimensional.
To assign an array to a list control in a Userform it needs to be a 2D array of values (recordsets scream out as the obvious use here - however the dimensions need to fit - so you then need to transpose your array to make it display properly).

In your case you can either opt for the AddItem method or build your array with a dummy column (and hide that column in the list control).
Be advised though that you can only redimension the last dimension of your array
e.g.
ReDim Preserve accountArr(1, 1 To x)
while preserving the contents.
But for the list array you'd want
ReDim Preserve accountArr(1 to x, 1)
which wouldn't be supported.
So you'd need to fill an appropriately sized array.

Cheers.

 

[fkegley]

I have had better luck doing this by assigning a range name to the cells I want in the combo box list. Then I set the Row Source property of the combo box to the range name.

 

[jal]

I have had better luck doing this by assigning a range name to the cells I want in the combo box list. Then I set the Row Source property of the combo box to the range name.

Not sure what the problem is. I just opened an Excel file and tried this:

For row = 0 To 50
Me.ComboBox1.AddItem CStr(row)
Next

which worked fine. This also worked, does the same thing.


For row = 0 To 50
Me.ComboBox1.AddItem
Me.ComboBox1.Column(0, row) = CStr(row)
Next

 

[jal]

This also worked:

Dim arr
arr = Array(1, 2, 3, 4, 5)
Me.ComboBox1.List = arr


All these attempts were done in Excel 2003 - I'm beginning to supsect either (1) your array isn't populated or (2) your loop isn't actually executing.