Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
filling a combo box with two Columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
filling a combo box with two Columns
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 '<<============= --- Regards, Norman "Jan T." wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
filling a combo box with two Columns
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" skrev i melding ... 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 '<<============= --- Regards, Norman "Jan T." wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
filling a combo box with two Columns
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("A1:D100") '<<=== CHANGE With Me.ComboBox1 .ColumnCount = Rng.Columns.Count .List = Rng.Value End With End Sub '<<============= --- Regards, Norman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
filling a combo box with two Columns
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? "Jan T." wrote: 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" skrev i melding ... 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 '<<============= --- Regards, Norman "Jan T." wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
filling a combo box with two Columns
Hi Jan,
Change: Set WB = Workbooks("Book1") '<<=== CHANGE to Set WB = Workbooks("MyBook.xls") '<<=== CHANGE (My test file had not been saved!) --- Regards, Norman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
filling a combo box with two Columns
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 "Norman Jones" skrev i melding ... 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("A1:D100") '<<=== CHANGE With Me.ComboBox1 .ColumnCount = Rng.Columns.Count .List = Rng.Value End With End Sub '<<============= --- Regards, Norman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
filling a combo box with two Columns
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 -- HTH :) Dana DeLouis Windows XP & Office 2007 "Jan T." wrote in message ... 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 "Norman Jones" skrev i melding ... 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("A1:D100") '<<=== CHANGE With Me.ComboBox1 .ColumnCount = Rng.Columns.Count .List = Rng.Value End With End Sub '<<============= --- Regards, Norman |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
filling a combo box with two Columns
Thank you very much for your answer. That's certainly a solution using
Transpose function. Regar Jan "Dana DeLouis" skrev i melding ... 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 -- HTH :) Dana DeLouis Windows XP & Office 2007 "Jan T." wrote in message ... 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 "Norman Jones" skrev i melding ... 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("A1:D100") '<<=== CHANGE With Me.ComboBox1 .ColumnCount = Rng.Columns.Count .List = Rng.Value End With End Sub '<<============= --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-Filling Related Combo Box Entries | Excel Programming | |||
filling text boxes from a combo box | Excel Programming | |||
Filling a Combo Box with VBA | Excel Programming | |||
filling combo box | Excel Programming | |||
Filling a combo box from another workbook | Excel Programming |