Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox and arrays
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox and arrays
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. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox and arrays
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" wrote in message ... 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. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox and arrays
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. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox and arrays
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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox and arrays
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" wrote in message ... 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. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox and arrays
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. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "Michael Malinsky" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Multicolumn Listbox and ordinary listbox | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |