Listbox problem

[Robert Crandal]

My spreadsheet contains data in 2 columns which looks
like this:

(A) (B)
--------------------------------
(row 1) Pears 0
(row 2) Oranges 10
(row 3) Apples 5
(row 4) Pumpkins 3
(row 5) Grapefruit 5
(row 6) Carrots 8
...
...
(row n) Plums 11

I want to run a macro which reads all the data from
column A (from row 1 to row "n") sorts the strings,
then displays the sorted strings in a listbox. So basically,
the user will see a sorted listbox of ONLY the fruits that occur
in column A.

If the user double clicks on a fruit item that is visible in
the listbox, I would like to display a second userform or
dialog box that shows the numeric quantity associated with
that fruit. This dialog box will let the user update or edit
the number quantity, which causes the data in column B
of the spreadsheet to be instantly updated.

Can anyone help me solve this problem?? I've tried several
approaches which have failed so far, so I'd like to hear some
new approaches that work.

Thank you!

 

[GS]

Robert Crandal explained on 2/15/2011 :

My spreadsheet contains data in 2 columns which looks
like this:

(A) (B)
--------------------------------
(row 1) Pears 0
(row 2) Oranges 10
(row 3) Apples 5
(row 4) Pumpkins 3
(row 5) Grapefruit 5
(row 6) Carrots 8
...
...
(row n) Plums 11

I want to run a macro which reads all the data from
column A (from row 1 to row "n") sorts the strings,
then displays the sorted strings in a listbox. So basically,
the user will see a sorted listbox of ONLY the fruits that occur
in column A.

If the user double clicks on a fruit item that is visible in
the listbox, I would like to display a second userform or
dialog box that shows the numeric quantity associated with
that fruit. This dialog box will let the user update or edit
the number quantity, which causes the data in column B
of the spreadsheet to be instantly updated.

Can anyone help me solve this problem?? I've tried several
approaches which have failed so far, so I'd like to hear some
new approaches that work.

Thank you!

Can you show us what you've been trying? OR are you just wanting
someone to write it for you?

Suggestion:
Would using a 2-column listbox with its 'Sorted' property set to 'True'
work? If so then you could use InputBox to get values from the user.
All could be handled by the listbox's DoubleClick event.

 

[GS]

GS formulated the question :

Would using a 2-column listbox with its 'Sorted' property set to 'True' work?

Oops! I forgot MSO listbox doesn't have a 'Sorted' property. So the
data needs to be sorted before adding to the list. -No problem!

 

[Robert Crandal]

Can you show us what you've been trying? OR are you just wanting someone
to write it for you?

Suggestion:
Would using a 2-column listbox with its 'Sorted' property set to 'True'
work? If so then you could use InputBox to get values from the user. All
could be handled by the listbox's DoubleClick event.

Sure, I can show you some bits of my code, but I'm also willing to
look at someone else's code for alternative solutions.

I first created a global array of strings in one module. The global
declaration looks like this:

Public g_Arr() As String ' My global array of strings

Then, when the userform is created during "UserForm_Initialize()",
I call "ReDim g_Arr" to resize that array to match the current number
of elements in column A. I then load all strings into the "g_Arr" array.
Next, I then call the function below to sort the array:

'---------------------------------------------------
Sub Sort_Array_of_Strings(Arr() As String) ' g_Arr is passed in here!

Dim i, j As Integer
Dim str1, str2 As String

For i = 0 To UBound(Arr)
For j = i To UBound(Arr)
If UCase(Arr(j)) < UCase(Arr(i)) Then
str1 = Arr(i)
str2 = Arr(j)
Arr(i) = str2
Arr(j) = str1
End If
Next j
Next i

End Sub
'------------------------------------------------

One of my main problems is that the sorted list of strings in the listbox
does
NOT match the unsorted order of the data on the spreadsheet, which
makes it hard to find the correct row index. Does that make sense?

Also, I would really prefer to solve this using a one column listbox,
not a two column listbox.

Does anyone have any other ideas? I would appreciate it.

- Robert C.

 

[GS]

Robert Crandal presented the following explanation :

Sure, I can show you some bits of my code, but I'm also willing to
look at someone else's code for alternative solutions.

I first created a global array of strings in one module. The global
declaration looks like this:

Public g_Arr() As String ' My global array of strings

Then, when the userform is created during "UserForm_Initialize()",
I call "ReDim g_Arr" to resize that array to match the current number
of elements in column A. I then load all strings into the "g_Arr" array.
Next, I then call the function below to sort the array:

'---------------------------------------------------
Sub Sort_Array_of_Strings(Arr() As String) ' g_Arr is passed in here!

Dim i, j As Integer
Dim str1, str2 As String

For i = 0 To UBound(Arr)
For j = i To UBound(Arr)
If UCase(Arr(j)) < UCase(Arr(i)) Then
str1 = Arr(i)
str2 = Arr(j)
Arr(i) = str2
Arr(j) = str1
End If
Next j
Next i

End Sub
'------------------------------------------------

One of my main problems is that the sorted list of strings in the listbox
does
NOT match the unsorted order of the data on the spreadsheet, which
makes it hard to find the correct row index. Does that make sense?

Also, I would really prefer to solve this using a one column listbox,
not a two column listbox.

Does anyone have any other ideas? I would appreciate it.

- Robert C.

Thanks! -This helps.

You could use a 1-column listbox.

Is there some reason why the list on the wks can't be sorted? This
would solve the issue of finding the item on the sheet.

Would you want the item to scroll to the top of the window? OR just
select the item, whereby it will scroll into view at top/bottom
accordingly?

Are you aware that setting the Rowsource property causes changes to
update in the listbox immediately? This means the macro can set the
value in the target cell and the listbox will update itself to reflect
that.

 

[Robert Crandal]

The spreadsheet data will be hidden from other users inside a
hidden worksheet. I could have the worksheet sort itself, but I prefer
to avoid this for now (for complex reasons).

Are you talking about a different type of listbox here? I'm just using a
regular one column listbox on a userform that has the ability to scroll the
items up and down. The user then double clicks the item of his choice.

I actually forgot about the "Rowsource" property, but I'm not sure if that
will
help in my situation. My worksheet contains a list of unsorted pairs of
fruits and numeric quantities, but the listbox contains a sorted list of
ONLY the fruits to choose from. If the user double clicks one of the fruit
items from the sorted listbox, how can I map that selection back to the
corresponding row on the unsorted list on the worksheet?

Thanks!

 

[GS]

Robert Crandal laid this down on his screen :

The spreadsheet data will be hidden from other users inside a
hidden worksheet. I could have the worksheet sort itself, but I prefer
to avoid this for now (for complex reasons).

The macro would do the sort, which would include all the data moving
with its respective cell in ColA.

Are you talking about a different type of listbox here? I'm just using a
regular one column listbox on a userform that has the ability to scroll the
items up and down. The user then double clicks the item of his choice.

That listbox can have as many columns as you want by specifying its
ColumnCount property.

I meant did you want the selected list item to be scrolled to OR
selected on the sheet so the user can read the current quantity after
double-clicking in the listbox?

I actually forgot about the "Rowsource" property, but I'm not sure if that
will
help in my situation. My worksheet contains a list of unsorted pairs of
fruits and numeric quantities, but the listbox contains a sorted list of
ONLY the fruits to choose from. If the user double clicks one of the fruit
items from the sorted listbox, how can I map that selection back to the
corresponding row on the unsorted list on the worksheet?

Well, this is why I was suggesting to sort the sheet and use the
RowSource property. This puts the row position in sync with
(listindex+1).

Otherwise, we need to put the list in an array and use the array index
to find the correct row. That precludes using a 2nd column in the
listbox so we can store the row number there, making this the
BoundColumn. when a user clicks on the fruit name the listbox's Value
property returns the row number. This 2nd column can be zero width so
it doesn't display, OR set the width of col1 to the same width as the
control.

 

[GS]

I have a working sample that sorts the list (not the sheet) and sets up
an index for the row# for each item. If you post your email address
I'll send it on.

 

[Robert Crandal]

Thank you. I really appreciate that!

Send it to

"jmmcinaz"

@

yahoo.com

(Take the double quotes off the username, but that's my email)

 

[Robert Crandal]

I'm using Excel 2007 and I couldn't find the "Sorted" property
anywhere. Do listboxes in Excel 2007 have that property?

Also, if I have a 2 column listbox, how do I get data into the
2nd column? I can get data into column 1 with the following:

Me.Listbox1.AddItem "Apple" ' puts Apple in row 1, col 1
Me.Listbox1.AddItem "Orange" ' puts Orange in row 2, col 1
'
' etc.. etc...

 

[GS]

I'm using Excel 2007 and I couldn't find the "Sorted" property
anywhere. Do listboxes in Excel 2007 have that property?

As I mentioned in my subpost, MSO listbox does not have this.

Also, if I have a 2 column listbox, how do I get data into the
2nd column? I can get data into column 1 with the following:

Me.Listbox1.AddItem "Apple" ' puts Apple in row 1, col 1
Me.Listbox1.AddItem "Orange" ' puts Orange in row 2, col 1
'
' etc.. etc...

My sample file shows how to do this. It uses a 2 col listbox w/Col2 as
Boundcolumn. User only sees names in the list (sorted) because Col2
width is zero.

 

[GS]

Robert Crandal used his keyboard to write :

Thank you. I really appreciate that!

Send it to

"jmmcinaz"

@

yahoo.com

(Take the double quotes off the username, but that's my email)

File is on its way!

 

[Robert Crandal]

..> My sample file shows how to do this. It uses a 2 col listbox w/Col2 as
Thanks so much for the demo. It is much appreciated.

In looking at your code, it appears that you are populating the multi-column
listbox with the following code:

Me.ListBox1.list = vItemList

Is "vItemList" a "Worksheet" type of variable that corrosponds to data
on the worksheet? (I'm still kinda newbie with VBA, so I just wanted
to verify)

Also, is that the ONLY way to fill in a 2 column listbox,
by passing a Worksheet/range to the ".list" property?? What if I have
data that doesn't exist on the worksheet and I want to put that data into
a 2 column, n-rows listbox?? For example, what if I have a mult-dim array
of strings....can I use this data to fill in my multi-column listbox?

 

[GS]

Robert Crandal wrote :

Thanks so much for the demo. It is much appreciated.

In looking at your code, it appears that you are populating the multi-column
listbox with the following code:

Me.ListBox1.list = vItemList

Is "vItemList" a "Worksheet" type of variable that corrosponds to data
on the worksheet? (I'm still kinda newbie with VBA, so I just wanted
to verify)

No. It's a VBA variant variable that I used to dump the original wks
list into. I also dumped the temp wks list into it since the original
data was no longer needed.

One thing to note about filling arrays from a wks; the 1st element is 1
not zero, AND it's a 2D array (#Rows x #Cols) even if there's only 1
col.

Also, is that the ONLY way to fill in a 2 column listbox,
by passing a Worksheet/range to the ".list" property??

No. (I didn't pass a wks range to the listbox. I passed a 2D array to
the listbox, which I populated from the temp wks) This is the quickest
(and easiest) way to read/write a range. It also happens to be the
quickest way to fill a listbox from an array. So then, there's 2
separate things happening: 1- range read/write, 2- populating a
multi-col listbox with a multi-dim array. Note, though, that the array
is dynamically sized as used here. This is preferred for unknown
amounts of data.

What if I have
data that doesn't exist on the worksheet and I want to put that data into
a 2 column, n-rows listbox?? For example, what if I have a mult-dim array
of strings....can I use this data to fill in my multi-column listbox?

Yes, in the same 'quick dump' fashion OR you can loop the array using
AddItem and the List property (specifying row/col positions). The
latter is slower by far. (Imagine a 10000 row by 20 col data table.
Would you loop or dump?)

So then, if you had such an array (10000,20) then you could set the
listbox ColumnCount to the UBound of the 2nd dim and dump the data into
it.