filling a combo box with two Columns

[Jan T.]

Hi.
How can I fill a Combo Box with two Columns?
Using ComboBox1.AddItem seems to only accept one column?

This is what I want the Combo Box to contain:
1 Dough Petersen
2 Cheap Hawley
3 Bill Smith
4 Bruce Jones
5 Bob Copeland

Using the Additem method of the Combo Box, it seems like I can only have
one column? How can I have several columns?

Thanks for your help in advanse.

Regards
Jan

 

[Norman Jones]

Hi Jan,

See The List and Column properties in VBA help.

For example:

'=============>>
Private Sub UserForm_Initialize()
Dim arr(0 To 4, 1 To 2)
arr(0, 1) = "Dough"
arr(0, 2) = "Petersen"
arr(1, 1) = "Cheap"
arr(1, 2) = "Hawley"
arr(2, 1) = "Bill"
arr(2, 2) = "Smith"
arr(3, 1) = "Bruce"
arr(3, 2) = "Jones"
arr(4, 1) = "Bob"
arr(4, 2) = "Copeland"

With Me.ComboBox1
.ColumnCount = 2
.List = arr
End With
End Sub
'<<=============

 

[Jan T.]

Thanx!
Well, now I want to do like your example but I want first to fill the Array
with
values from a db. I dont know how many items it will contain. When using the
Redim Preserve,
I am only allowed to change the Ubound of my Array wich seems to just give
me
more Columns and not more Rows. How do I solve this? Any idea?

Jan

 

[Norman Jones]

Hi Jan,

'-----------------
Well, now I want to do like your example but I want first
to fill the Array with values from a db. I dont know how
many items it will contain. When using the Redim Preserve,
I am only allowed to change the Ubound of my Array wich
seems to just give me more Columns and not more Rows.
How do I solve this? Any idea?
'-----------------

Perhaps, try something like:

'=============>>
Private Sub UserForm_Initialize()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range

Set WB = Workbooks("Book1") '<<=== CHANGE
Set SH = WB.Sheets("Sheet3") ' <<=== CHANGE
Set Rng = SH.Range("A1100") '<<=== CHANGE

With Me.ComboBox1
.ColumnCount = Rng.Columns.Count
.List = Rng.Value
End With
End Sub
'<<=============

 

[Joel]

Do you know the max size of the database? You can always over size the array
dimensions. It uses more memory, but memory is relatively cheap these days.
The combo box will only display the number of items put into the box. Justt
dim the array at 1000. Are you putting more than 1000 items into the
combobox?

 

[Norman Jones]

Hi Jan,

Change:

Set WB = Workbooks("Book1") '<<=== CHANGE

to

Set WB = Workbooks("MyBook.xls") '<<=== CHANGE


(My test file had not been saved!)

 

[Jan T.]

Okey, I might have found a solution. What I do is I don't dimention the
array
before I know number of records. Then I dimention the array.

Here's my code; (I get the data from a .mdb database file).

Function varEmplNames() As Variant
Dim varTemp() As Variant
Dim i As Integer
Dim oCnn As Object
Dim oRst As Object
Set oCnn = CreateObject("ADODB.Connection")
Set oRst = CreateObject("ADODB.Recordset")
i = 0
' open the connection
With oCnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open gstrOvertidDBfil ' global variabel holding the file path...
End With
' Define the Recordset
oRst.CursorLocation = adUseServer
' open the table
oRst.Open Source:="EmployeeNames", _
ActiveConnection:=oCnn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
' Check for number of items in db record.
Do
i = i + 1
oRst.MoveNext
Loop While Not oRst.EOF ' Get number of records to dimention the
array...
oRst.MoveFirst
ReDim varTemp(i, 1)
i = 0
Do
varTemp(i, 0) = oRst("EmplId")
varTemp(i, 1) = oRst("FirstName") & " " & oRst("LastName")
i = i + 1
oRst.MoveNext
Loop While Not oRst.EOF
' Close
oRst.Close
oCnn.Close
varEmplNames = varTemp
End Function

------------------------
Well, I wish there was an easier way to count number of records, like
oRst.Count
or something. Anyway, this function seem to works.

If anybody have moore sufficiant code, I will be glad to know.

Thanks, again.

Regards
Jan

 

[Dana DeLouis]

Hi. See if using the Dictionary or Collection would work for you.
I prefer the Dictionary.

You might be interested in looking at the following:
The Dictionary object helps to avoid such code.

Sub Demo()
Dim v()
ReDim v(1 To 3, 1 To 2)
v(1, 1) = 11
v(1, 2) = "Joe"
v(2, 1) = 12
v(2, 2) = "Bill"
v(3, 1) = 13
v(3, 2) = "Sue"

'Hmmm. Need to add 1 Record

With WorksheetFunction
v = .Transpose(v)
ReDim Preserve v(1 To 2, 1 To 4)
v = .Transpose(v)
End With
v(4, 1) = 14
v(4, 2) = "New Guy"
End Sub

 

[Jan T.]

Thank you very much for your answer. That's certainly a solution using
Transpose function.
Regar
Jan