Regarding getting values for Multi Column LISTBOX

[avadhutd2]

Hi,

Query – How to access the element in the columns of a listbox where there are 2 or more columns?

The background below resembles my previous query posted yesterday -
I have a listbox that have 2 columns. First column is having "Name" and another is "Data Type".

I am having another listbox where I can transfer items from LISTBOX 1 to LISTBOX 2.

I am forming a SQL query based on selections available in LISTBOX 2.
That is....if user selects 3 items ...A, B & C then I will be having a query as -
SELECT A, B, C from DBNAME

My query is as follows:

In order to have the entire column formatted for the selected item in LISTBOX 2 in m-dd-yyyy where the column 2 in LISTBOX2 is “Date” I got resolution as -

If UserForm2.ListBox2.List(1, 1) = "DATE" Then
Sheet1.Columns(2).NumberFormat = "mm-dd-yyyy"
End If



But the problem is I am not able to get the value when I do ListBox2.List(1,1).

Please find attached a sample excel worksheet - "Multi_Column_ListBox.xls" . Please refer USERFORM1 code.

I had added 2 list listboxes & a button “>>” that transfers items to LISTBOX2.

I need to see whatever item I select from LISTBOX1 to LISTBOX2 one below the other & in two columns (currently I am getting them one below the other).

Can any one guide me here??

[Tinbendr]

Your spreadsheet is blank. (When viewed in 2003)

[Tinbendr]

But try..
If UserForm2.ListBox2.List(, 1) = "DATE" Then

[avadhutd2]

Hi,

Thanks for your reply...I will surely try that. Meanwhile just want you to know as mentioned in query above - that I just had added a code in the USERFORM1 & not in any of the sheets.

Can you please refer USERFORM1 code ?

I had shown 2 ListBoxes & a ">>" button with code behind it as a sample to my query.

Please let me know if that is ok?

Thanks again for getting back with your comments!

[Tinbendr]

Can you please refer USERFORM1 code ?

:doh: I had a similarly name file and I was looking at it.

This was the only way I could make it work.

Private Sub SelectItems()
Dim eCount As Long
With ListBox1
'MsgBox .ListCount
For eCount = .ListCount - 1 To 0 Step -1
If .Selected(eCount) = True Then ' if selected then
ListBox2.AddItem .List(eCount, 0) 'add to listbox2, Column 0
ListBox2.Column(1, ListBox2.ListCount - 1) = .List(eCount, 1) 'add to listbox2, Column 1
.RemoveItem eCount ' remove
End If
Next

End With
End Sub

[avadhutd2]

With the background above - Need help to access 2nd Column in a LISTBOX 2 in order to identify & format a DATE TYPE field.

Both Parts are having problem – Can anyone get it resolved for me ??

(Part - A) While transferring from ListBox1 to ListBox2 – as we need to transfer the 2nd column i.e Data Type too!

Dim e As Long
Dim tarray(68, 2)

With ListBox1
If MoveAll = True Then
For e = 0 To .ListCount - 1
ListBox2.AddItem .List(e) 'move all items to To box
Next
.Clear
Else
For e = .ListCount - 1 To 0 Step -1
If .Selected(e) = True Then ' if selected then
ListBox2.AddItem .List(e), 1 ' add to other list ListBox2.Column(1, ListBox2.ListCount - 1) = .List(e, 1)
.RemoveItem e ' remove
End If
Next
End If
End With

Here items are appearing one below other & not column wise.

Alternately I tried using an array while transferring the items from ListBox1 to ListBox2 as –

'With ListBox1
If MoveAll = True Then
For e = 0 To .ListCount - 1
'ListBox2.AddItem .List(e) 'move all items to Tobox
tarray(e, 0) = .List(e, 0)
tarray(e, 1) = .List(e, 1)
ListBox2.List() = tarray
Next
.Clear
Else
For e = .ListCount - 1 To 1 Step -1
If .Selected(e) = True Then ' if selected then
ListBox2.AddItem .List(e, 0), ListBox2.ListCount ‘ earlier I tried this
ListBox2.AddItem .List(e, 1), ListBox2.ListCount
'.RemoveItem e ' remove

tarray(e, 0) = .List(e, 0)
tarray(e, 1) = .List(e, 1)
ListBox2.List() = tarray
.RemoveItem e ' remove
End If
Next
End If

End With

But here earlier items are getting replaced by the transferred items. I want earlier items to be retained.

The problem is I am not getting the ListBox 2 – 2nd column after transfer & can see EMPTY value while debugging - refer code below.


(Part - B) While formatting the field for DATE in the Sheet –

Dim e As Long

For e = 0 To ListBox2.ListCount - 1
If ListBox2.List(e, 1) = "Date" Then
Sheet1.Columns(e + 4).NumberFormat = "m/d/yyyy"
End If
Next

I also tried using an array – But this not getting the formatting done

Dim e As Long
Dim tarray(68, 2)

ListBox1.List() = tarray
ListBox2.List() = tarray

For e = 0 To ListBox2.ListCount - 1
If tarray(e, 1) = "Date" Then
Sheet1.Columns(e + 4).NumberFormat = "m/d/yyyy"
End If

Next

Please let me know...Thanks!