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.

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.

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.

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.

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

--