Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
List box with 2 columns
Hello
I am populating a list box with the field names from a recordset from an access database using an Excel form in VB. However, i have done the part that populates the field names, now i wish to have a 2nd column that contains the fields data type. How do i add items, currently i have: userform1.listbox1.additem rst.fields(x).name i also want to add alond side its name rst.fields(x).type. How do i add them so the field name is column 1, and the type is column 2. TIA Nath. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
List box with 2 columns
It might be easiest to populate the list box via an array:
Private Sub UserForm_Initialize() Dim Arr(1 To 2, 1 To 2) As String Arr(1, 1) = "AAA" Arr(1, 2) = "111" Arr(2, 1) = "BBB" Arr(2, 2) = "222" ListBox1.List = Arr End Sub where ListBox1 has a ColumnCount of at least 2. Alternatively: Private Sub UserForm_Initialize() ListBox1.AddItem "AAA" ListBox1.AddItem "BBB" ListBox1.Column(1, 0) = "111" ListBox1.Column(1, 1) = "222" End Sub -- Jim Rech Excel MVP "Nath" wrote in message ... | Hello | | I am populating a list box with the field names from a | recordset from an access database using an Excel form in | VB. However, i have done the part that populates the | field names, now i wish to have a 2nd column that contains | the fields data type. How do i add items, currently i | have: | | userform1.listbox1.additem rst.fields(x).name | | i also want to add alond side its name | | rst.fields(x).type. | | How do i add them so the field name is column 1, and the | type is column 2. | | | TIA | | Nath. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
List box with 2 columns
Hi Nath,
Here is a simple example. First, set the ColumnCount property of the listbox to 2 With ListBox1 .AddItem rst.fields(x).name .List(.ListCount - 1, 1) = rst.fields(x).type End With -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Nath" wrote in message ... Hello I am populating a list box with the field names from a recordset from an access database using an Excel form in VB. However, i have done the part that populates the field names, now i wish to have a 2nd column that contains the fields data type. How do i add items, currently i have: userform1.listbox1.additem rst.fields(x).name i also want to add alond side its name rst.fields(x).type. How do i add them so the field name is column 1, and the type is column 2. TIA Nath. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
List box with 2 columns
With Userform1.listbox1
.AddItem rst.fields(x).name 'first column .List(.ListCount - 1, 1) = rst.fields(x).type '2nd column '... End With "Nath" wrote in message ... Hello I am populating a list box with the field names from a recordset from an access database using an Excel form in VB. However, i have done the part that populates the field names, now i wish to have a 2nd column that contains the fields data type. How do i add items, currently i have: userform1.listbox1.additem rst.fields(x).name i also want to add alond side its name rst.fields(x).type. How do i add them so the field name is column 1, and the type is column 2. TIA Nath. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
List box with 2 columns
"Bob Phillips" wrote ...
Here is a simple example. First, set the ColumnCount property of the listbox to 2 With ListBox1 .AddItem rst.fields(x).name .List(.ListCount - 1, 1) = rst.fields(x).type End With Extending this: Sub Test() With ListBox1 .AddItem rst.fields(x).name .List(.ListCount - 1, 1) = GetDataType(rst.fields(x).type) End With End Sub Public Function GetDataType( _ DataType As ADODB.DataTypeEnum, _ Optional ByVal EnumOnly As Boolean = False _ ) As String If EnumOnly Then Select Case DataType Case 0: GetDataType = "adEmpty" Case 2: GetDataType = "adSmallInt" Case 3: GetDataType = "adInteger" Case 4: GetDataType = "adSingle" Case 5: GetDataType = "adDouble" Case 6: GetDataType = "adCurrency" Case 7: GetDataType = "adDate" Case 8: GetDataType = "adBSTR" Case 9: GetDataType = "adIDispatch" Case 10: GetDataType = "adError" Case 11: GetDataType = "adBoolean" Case 12: GetDataType = "adVariant" Case 13: GetDataType = "adIUnknown" Case 14: GetDataType = "adDecimal" Case 16: GetDataType = "adTinyInt" Case 17: GetDataType = "adUnsignedTinyInt" Case 18: GetDataType = "adUnsignedSmallInt" Case 19: GetDataType = "adUnsignedInt" Case 20: GetDataType = "adBigInt" Case 21: GetDataType = "adUnsignedBigInt" Case 64: GetDataType = "adFileTime" Case 72: GetDataType = "adGUID" Case 128: GetDataType = "adBinary" Case 129: GetDataType = "adChar" Case 130: GetDataType = "adWChar" Case 131: GetDataType = "adNumeric" Case 132: GetDataType = "adUserDefined" Case 133: GetDataType = "adDBDate" Case 134: GetDataType = "adDBTime" Case 135: GetDataType = "adDBTimeStamp" Case 136: GetDataType = "adChapter" Case 138: GetDataType = "adPropVariant" Case 139: GetDataType = "adVarNumeric" Case 200: GetDataType = "adVarChar" Case 201: GetDataType = "adLongVarChar" Case 202: GetDataType = "adVarWChar" Case 203: GetDataType = "adLongVarWChar" Case 204: GetDataType = "adVarBinary" Case 205: GetDataType = "adLongVarBinary" End Select Else Select Case DataType Case 0: GetDataType = "no value" Case 2: GetDataType = "a two-byte signed integer" Case 3: GetDataType = "a four-byte signed integer" Case 4: GetDataType = "a single-precision floating-point value" Case 5: GetDataType = "a double-precision floating-point value" Case 6: GetDataType = "a currency value" Case 7: GetDataType = "a date value" Case 8: GetDataType = "a null-terminated character string" Case 9: GetDataType = "a pointer to an IDispatch" & _ " interface on a COM object" Case 10: GetDataType = "a 32-bit error code" Case 11: GetDataType = "a boolean value" Case 12: GetDataType = "an Automation Variant" Case 13: GetDataType = "a pointer to an IUnknown interface" & _ " on a COM object" Case 14: GetDataType = "an exact numeric value with a fixed" & _ " precision and scale" Case 16: GetDataType = "a one-byte signed integer" Case 17: GetDataType = "a one-byte unsigned integer" Case 18: GetDataType = "a two-byte unsigned integer" Case 19: GetDataType = "a four-byte unsigned integer" Case 20: GetDataType = "an eight-byte signed integer" Case 21: GetDataType = "an eight-byte unsigned integer" Case 64: GetDataType = "a 64-bit value representing " & _ " the number of 100-nanosecond" & _ " intervals since January 1, 1601" Case 72: GetDataType = "a globally unique identifier" Case 128: GetDataType = "a binary value" Case 129: GetDataType = "a string value" Case 130: GetDataType = "a null-terminated Unicode character" & _ " string" Case 131: GetDataType = "an exact numeric value with a fixed" & _ " precision and scale" Case 132: GetDataType = "a user-defined variable" Case 133: GetDataType = "a date value" Case 134: GetDataType = "a time value" Case 135: GetDataType = "a date/time stamp" Case 136: GetDataType = "a four-byte chapter value that" & _ " identifies rows in a child rowset" Case 138: GetDataType = "an Automation PROPVARIANT" Case 139: GetDataType = "a numeric value" Case 200: GetDataType = "a string value" Case 201: GetDataType = "a long string value" Case 202: GetDataType = "a null-terminated Unicode" & _ " character string" Case 203: GetDataType = "a long null-terminated Unicode" & _ " string value" Case 204: GetDataType = "a binary value" Case 205: GetDataType = "a long binary value" End Select End If End Function -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alphabetical List Of Columns | Excel Discussion (Misc queries) | |||
Shorten list into several columns | Excel Discussion (Misc queries) | |||
Splitting a list over 2-3 columns | Excel Discussion (Misc queries) | |||
list turned into two columns | Excel Discussion (Misc queries) | |||
Need to add mutliple columns to a List Box | Excel Programming |