ListBox and arrays

[Michael Malinsky]

I have UserForm1 with TextBox1, TextBox2, TextBox3, ListBox1,
CommandButton1, and CommandButton2.

The user enters information in the 3 TextBoxes, then presses CommandButton1
to add that info to ListBox1. The code for CommandButton1 is:

Private Sub CommandButton1_Click()

x = lbRangeList.ListCount

'If Item2 is higher than Item3, display a message box
'and do not update ListBox1 to include the range. Otherwise, update the
list.

If Val(TextBox2.Value) < Val(TextBox2.Value) Then
ReDim Preserve Item1(x), Item2(x), Item3(x)
Item1(x) = TextBox1.Value
Item2(x) = TextBox2.Value
Item3(x) = TextBox3.Value
ListBox1.AddItem Item1(x)
ListBox1.List(ListBox1.ListCount - 1, 1) = Item2(x)
ListBox1.List(ListBox1.ListCount - 1, 2) = Item3(x)
x = x + 1 'Add 1 to x for the next item
End If
Else
a = MsgBox("Item1 cannot be higher than Item2. Please re-enter your
data.", vbCritical, "Invalid Entry")
End If
'Clear textboxes
For Each ctrl In Me.Controls
If TypeName(ctrl) = "TextBox"
Next ctrl
Item1.SetFocus
lbRangeList.ListIndex = -1

End Sub

I put these items in arrays (declared as public variables) for using the
arrays in other parts of my project. Here's my problem: I want to be able
to remove a line from ListBox1 by pressing CommandButton2. I can do this
easily enough, but my problem is in how to update the arrays I've created to
account for items I've removed from ListBox1. Currently, I have the
following code for ListBox2:

Sub CommandButton2_Click()

ListBox1.RemoveItem lbRangeList.ListIndex

End Sub

Which removes the items from ListBox1, but when I continue to run other
subroutines in the module, I get a subscript out of range error which I'm
assuming has to do with removing an item from ListBox1, but not updating the
arrays to account for this change.

Any help would be greatly appreciated.

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh

 

[Tom Ogilvy]

For a simple array structure
you would have to locate the item in the array, move all the items above it
down one slot and resize the array. This would require a loop.


You might explore using a collection.

 

[Michael Malinsky]

So if I were to maintain an array structure, I'd have a loop essentially
saying:

Item1(x-1)=Item1(x)
Redim Preserve Item1(x-1)

I'm not familiar with collections. Any recommendaitons on a good source to
determine if this might be a more viable solution?

Thanks.
--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh

 

[Tom Ogilvy]

John Walkenbach uses a custom collection in his routine to fill a listbox
with unique items.

http://j-walk.com/ss/excel/tips/tip47.htm

It demonstrates some of the key aspects of creating and using a collection.

A collection has a remove method, so you can remove your item directly
without having to manage the storage of the items in the collection. You
can also assign an index value, so you can use that or the numerical index
to retrieve items.

You use collections all the time in Excel - so I guess you mean you are
unfamiliar with creating a collection.

 

[Michael Malinsky]

Yes, I meant that I'm not familiar with creating collections. I'll take a
look at the link you posted.

Thanks.

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh

 

[Michael Malinsky]

Tom,

I looked at John Walkenbach's tip you referred me to. I don't think this
will work for me unless I'm missing something.

My project allows the user to enter information in three textboxes.
TextBox1 is a description, TextBox2 is a beginning number, and TextBox2 is
an end number. On clicking a commandbutton, a new line in a listbox is
created with the information from the three textboxes on a single line while
also updating arrays Description(x), BottomRange(x), and TopRange(x). Each
line created in the listbox could have some overlap/duplication in the
numbers entered by the user. After this info is entered, the project
randomly selects numbers from each range. The following code shows how I am
using the arrays to come up with a desired list. It doesn't seem as if a
collection would be the way to go, but as I said, I'm not familiar with
creating collections, so I may be missing something.

SampleSize = TextBox3.Value

'Calculate total population
Population = 0
For y = 0 To x
Population = Population + TopRange(y) - BottomRange(y) + 1
Next y

ReDim RandomNumber(Population)
ReDim arr(1 To Population)

'Thanks to Tom Ogilvy
j = 0
For i = LBound(BottomRange) To UBound(BottomRange)
For j = BottomRange(i) To TopRange(i)
k = k + 1
arr(k) = rangename(i)
Next
Next
'End thanks to Tom Ogilvy

RandomNumber(0) = -1
For y = 1 To SampleSize
RandomNumber(y) = Int(Population * Rnd + 1)
Cells(y, 1).Value = RandomNumber(y)
Cells(y, 2).Value = arr(RandomNumber(y))
Next y

Unload UserForm1

Thanks.

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh

 

[Tom Ogilvy]

if BottomRange, TopRange and Description were collections

you wouldn't need to change the code you show much except that the
collection starts with 1 as an index, so

For y = 1 to BottomRange.count
Population = Population + TopRange(y) - BottomRange(y) + 1
Next y

and

For i = 1 to BottomRange.count
For j = BottomRange(i) To TopRange(i)
k = k + 1
arr(k) = rangename(i)
Next
Next


You don't have to use an index value, so you can have duplicates in a
collection.

In Johns code, rather than have


NoDupes.Add Cell.Value, CStr(Cell.Value)

use

NoDupes.Add Cell.Value

as an example.