How to loop through multi-selection listbox?

[dan dungan]

Hi,

In an Excel 2000 spreadsheet on Windows XP,
I have a multi-selection listbox, lbxQuanity, on UserForm2 that
displays numbers from 1 to 12500.

This is so the employee can choose the quantities in a quote
calculation. A customer could request pricing for up to 3 quantities
for each price break.

These are the 10 price breaks:
1-9, 10-19, 20-49, 50-99, 100-249, 250-499, 500-999,
1000-2499, 2500-4999, 5000 & Up

I need to populate cells with the quantities the employee has
selected.

The first selection in price break 1-9 should populate cell E83
The second selection if there is one for this price break should
populate cell E89.
And the third selection, if there is one, should populate cell E95

Any selections in the next price break, 10-19, would populate
F83, F89 and F95.

The selections for price break 20-49 would populate G83, G89 and G95.
And so on for each price break.

In the click event of a command button, CommandButton1, I've
begun with the following code:

'The message boxes are there just for testing.

Private Sub CommandButton1_Click()
Dim i As Integer 'counter
Dim NothingThere As Boolean 'flag for no selection
NothingThere = True
For i = 0 To lbxQuantity.ListCount - 1 'loop the list
If lbxQuantity.Selected(i) Then
MsgBox lbxQuantity.List(i) & " is selected. Action here."
NothingThere = False
End If
Next
If NothingThere = True Then
MsgBox "No selection is made, whatever here"
End If
End Sub

I don't know which order to test which price break the selection
belongs in.

I don't know how to determine how many selections exist for each price
break so I can place the value in the appropriate cell.

Does anyone have any suggestions?

Thanks,

Dan Dungan

 

[Dick Kusleika]

Hi,

In an Excel 2000 spreadsheet on Windows XP,
I have a multi-selection listbox, lbxQuanity, on UserForm2 that
displays numbers from 1 to 12500.

These are the 10 price breaks:
1-9, 10-19, 20-49, 50-99, 100-249, 250-499, 500-999,
1000-2499, 2500-4999, 5000 & Up


The first selection in price break 1-9 should populate cell E83
The second selection if there is one for this price break should
populate cell E89.
And the third selection, if there is one, should populate cell E95

Any selections in the next price break, 10-19, would populate
F83, F89 and F95.

This seems to work

Private Sub CommandButton1_Click()

Dim vaBreaks As Variant
Dim i As Long, j As Long
Dim lQtyCnt As Long

vaBreaks = VBA.Array(1, 10, 20, 50, 100, 250, 500, 1000, 2500, 5000,
12501)

For i = 0 To 9
lQtyCnt = 0
For j = vaBreaks(i) - 1 To vaBreaks(i + 1) - 1 - 1
If Me.lbxQuantity.Selected(j) Then
lQtyCnt = lQtyCnt + 1
If lQtyCnt <= 3 Then
Sheet1.Range("f1").Offset(76 + (lQtyCnt * 6), i).Value =
_
Me.lbxQuantity.List(j)
End If
End If

Next j
Next i

End Sub

 

[dan dungan]

Hi Dick,

It works perfectly except I had to change the range constant from
"f1" to "e1" in the line,
Sheet1.Range("f1").Offset(76 + (lQtyCnt * 6), i).Value =

I have examined the code using F8, and I think I see
how the code works. But I still don't understand how
you developed it.

If you have time to explain, here are some of the parts I don't
understand:

1. What's the purpose of typing vaBreaks as Variant?
2. Why subtract 1 twice in the line:
For j = vaBreaks(i) - 1 To vaBreaks(i + 1) - 1 - 1

3. How did you know which component to deal with first?
Did you start with a formula? Please talk about your design
process.

Thanks for your time.

Dan

 

[dan dungan]

I guess I spoke too soon. I hope there's a reason for this.

This code works great on my test spreadsheet.

When I applied the code in the actual application, the output is good
for the first price break, but quantities in the other price breaks
are offset to the next column.

Price Break Column Returned Data
1-9 E 8
10-19 F
20-49 G 10
50-99 H 20
100-249 I 50
250-499 J 100
500-999 K 250
1000-2499 L 500
2500-4999 M 1000
5000 & Up N 2500

If I pick a quantity over 5000, the program crashed because the cells
in column O are protected and locked.

The only differences that I've noted are:

1. I changed this line from:
Sheet1.Range("e1").Offset(76 + (lQtyCnt * 6), i).Value = _
to
Sheet6.Range("e1").Offset(76 + (lQtyCnt * 6), i).Value = _

2. The Application has the ListBox and CommandButton on a
UserForm. On the test worksheet, I ran the
ListBox and CommandButton reside on the worksheet.
The ListBoxes don't have the same properties.
The ListBox on the spreadsheet shows ListFillRange.
The ListBox on the UserForm shows RowSource

So, would that affect the way the code runs?

How can I figure out what's going on here?

Thanks,

Dan

 

[Dick Kusleika]

I guess I spoke too soon. I hope there's a reason for this.

This code works great on my test spreadsheet.

When I applied the code in the actual application, the output is good
for the first price break, but quantities in the other price breaks
are offset to the next column.

Price Break Column Returned Data
1-9 E 8
10-19 F
20-49 G 10
50-99 H 20
100-249 I 50
250-499 J 100
500-999 K 250
1000-2499 L 500
2500-4999 M 1000
5000 & Up N 2500

If I pick a quantity over 5000, the program crashed because the cells
in column O are protected and locked.

On mine (changing the referent to E1), mine starts in E and ends in N. It's
almost as if you have one too many elements in vaBreaks. Put a Stop between
the vaBreaks line and the For i line, so it looks like this

vaBreaks = VBA.Array(1, 10, 20, 50, 100, 250, 500, 1000, 2500, 5000,
12501)

Stop

For i = 0 To 9

(without the word wrap). Now run the code and execution should halt on the
Stop line. In break mode, look at the locals window (View > Locals). Expand
vaBreaks and you should have an 11 element array (vaBreaks(0) to
vaBreaks(10)). If you have more, you might have a stray comma in there or
something.

The only differences that I've noted are:

1. I changed this line from:
Sheet1.Range("e1").Offset(76 + (lQtyCnt * 6), i).Value = _
to
Sheet6.Range("e1").Offset(76 + (lQtyCnt * 6), i).Value = _

2. The Application has the ListBox and CommandButton on a
UserForm. On the test worksheet, I ran the
ListBox and CommandButton reside on the worksheet.
The ListBoxes don't have the same properties.
The ListBox on the spreadsheet shows ListFillRange.
The ListBox on the UserForm shows RowSource

Those shouldn't matter.

Two options if the above doesn't reveal the problem. You can copy and paste
your code in reply and I'll see if I can see any differences that may be
escaping you. Or you can send the workbook that's failing to
(e-mail address removed) and I'll look at it.

 

[Dick Kusleika]

If you have time to explain, here are some of the parts I don't
understand:

1. What's the purpose of typing vaBreaks as Variant?

I knew that I would need to code the price breaks into the procedure since
there's nothing in the listbox that indicates where a price break. I
decided to put break points in an array which left me with two options. I
could dimension an array variable and hard code each starting point into
each element, like

Dim aBreaks() as Long

aBreaks(0) = 1
aBreaks(1) = 10
....
aBreaks(10) = 12501

My second option, the option I chose, was to use the Array function. The
Array function returns a Variant array, so I had to use a Variant variable
to hold it.

2. Why subtract 1 twice in the line:
For j = vaBreaks(i) - 1 To vaBreaks(i + 1) - 1 - 1

That looks a little wierd, I'll admit, but it's easier for me to read. When
I'm looping through a listbox, it usually looks like

For i = 0 to Me.ListBox1.ListCount - 1
....
Next i

So I took that basic construct and subtracted 1 from both ends of it. Normal
people would just subract two, but when I look at this, I see my normal
listbox looping structure with one subracted from each end. Seeing that, I
immediately recongize that I was dealing with an off-by-one problem and I
don't have to try to figure out the significance of '2'.

3. How did you know which component to deal with first?
Did you start with a formula? Please talk about your design
process.

I thought of this problem as you having 10 listboxes in one. If you
actually had 10 listboxes on your form, you would just loop through the
listboxes and process them separately. So I tried to do the same thing by
using an array to define where my "virtual listboxes" existed inside your
big listbox. The outer loop (For i) loops through the virtual listboxes and
the inner loop (For j) loops through each of them as if they were their own
listbox. Then I just keep a count so I can stop processing after three
hits.

 

[dan dungan]

Hi Dick,

I entered the stop command as you directed.

Both workbooks held the 11 element array.

The development workbook still shows errors in data placement.

I compared the test and development workbooks' code and they appear
identical except for sheet name differences.

I've examined this and don't see why the data is going to the wrong
cells
for quantities in all but the first array element.

I'm sending the development book via email for your feedback.

Thanks,

Dan

 

[dan dungan]

Hi Dick,

I really appreciate your time and explanations.

Thank you very much,

Dan

 

[Dick Kusleika]

I'm sending the development book via email for your feedback.

Dan: I haven't seen it, so if you sent it already, send it again to
(e-mail address removed). Otherwise I'll keep my out for it and I'm sure we'll
get it solved.

Thanks,

 

[dan dungan]

Hi Dick,

I guess the file was too large to send via email. I've reduced the
file size to 754 KB and resent.

Dan

 

[dan dungan]

Hi Excel programmers,

I've described my questions. And then further down in the post,
I've described the details.

Here are my questions:
1. Is there a way to loop through textboxes?
2. How can I add a new textbox if the rep
has another quantity to enter?
3. How can I loop through the textboxes and
place the textbox value in the appropriate cell for
both the quantities and delivery times?

Here's the background:

Customer service reps are using a spreadsheet I developed in Excel
2000 to prepare quotes.

1. the quote could contain several part numbers.
2. each part number could contain several quantities.
3. There are 10 price breaks for the quantities:

1-9, 10-19, 20-49, 50-99, 100-249, 250-499, 500-999,
1000-2499, 2500-4999, 5000 & Up

4. There is a delivery time associated with each item in the quote.

At this time, the reps enter the up to three quantities for each price
break in a cell.

For example,

for the quantities with delivery time in weeks

3, 8
5, 7
7, 9
11, 5
24, 9
25 8

Quantity
Column E Column F Column G etc.
1-9, 10-19, 20-49,
etc.
Row 83 3 11 24
Row 89 5 25
Row 95 7


Delivery in Weeks
Column E Column F Column G etc.
Row 86 8 5 9
Row 92 7 8
Row 98 9

I'm trying to simplify data entry for the reps, so I'm designing
a userform for data entry.

Back on March 19, I described this (minus the delivery time) and
received the following code from Dick Kusleika to use
in a command button with a listbox.
___________________________________________________

Private Sub CommandButton1_Click()

Dim vaBreaks As Variant
Dim i As Long, j As Long
Dim lQtyCnt As Long

vaBreaks = VBA.Array(1, 10, 20, 50, 100, 250, 500, 1000, 2500, 5000,
12501)

For i = 0 To 9
lQtyCnt = 0
For j = vaBreaks(i) - 1 To vaBreaks(i + 1) - 1 - 1
If Me.lbxQuantity.Selected(j) Then
lQtyCnt = lQtyCnt + 1
If lQtyCnt <= 3 Then
Sheet1.Range("f1").Offset(76 + (lQtyCnt * 6), i).Value =
_
Me.lbxQuantity.List(j)
End If
End If

Next j
Next i

End Sub

__________________________________________________

The code accomplished the goal; however, the listbox is too cumbersome
for the users with too many chances for errors.

I'm having difficulty converting these ideas to use with textboxes.

I understand one cannot have an array of textboxes. Well,
Chip Pearson describes using a class module, but I do not
understand how to apply that in my situation.

Here's what I've tried,
______________________________________________________
Private Sub CommandButton1_Click()

Dim aBreaks(10) As Long 'to set up the price breaks
Dim i(8) As MSForms.Textbox 'hold the textbox value
Dim j As Long 'Determine the price breaks
Dim lQtyCnt As Long 'to check how many quantities for each aBreak

aBreaks(0) = 1
aBreaks(1) = 10
aBreaks(2) = 20
aBreaks(3) = 50
aBreaks(4) = 100
aBreaks(5) = 250
aBreaks(6) = 500
aBreaks(7) = 1000
aBreaks(8) = 2500
aBreaks(9) = 5000
aBreaks(10) = 12501

For i = 0 To MSForms.Textbox.Count - 1
lQtyCnt = 0
For j = LBound(aBreaks()) - 1 To UBound(aBreaks()) - 1
If (j) Then
lQtyCnt = lQtyCnt + 1
If lQtyCnt <= 3 Then
Sheet1.Range("e1").Offset(76 + (lQtyCnt * 6),
iCtr).Value = _
(j)
End If
End If
Next j
Next i
End Sub

_____________________________________________________

but the line,

For i = 0 To MSForms.Textbox.Count - 1

returns the message,

"Compile error:
Method or data member not found"

Can someone help clarify this?

Thanks,

Dan

 

[Dick Kusleika]

but the line,

For i = 0 To MSForms.Textbox.Count - 1

returns the message,

"Compile error:
Method or data member not found"

Dim ctl as Control

For Each ctl in Me.Controls
If Typename(ctl) = "TextBox" Then
...
End If
Next ctl

 

[dan dungan]

Hello programmers,

Ok, now I've got the following code, but the line:

For j = aBreaks(i) - 1 To ctl.Value = (aBreaks(i + 1)) - 1

returns the error:

object variable or with block variable not set.

I don't know what to set the object variable to.

Any suggestions?

Thanks,

Dan

__________________________________________________
Private Sub CommandButton1_Click()

Dim aBreaks(10) As Long 'to set up the price breaks
Dim i As Long
Dim ctl As Control 'Returns the value held in the textboxes
Dim j As Long 'Determine where the textbox value
'falls in the price breaks
Dim lQtyCnt As Long 'to check how many quantities for each
' aBreak. No more than 3 quantities in each price break.

aBreaks(0) = 1
aBreaks(1) = 10
aBreaks(2) = 20
aBreaks(3) = 50
aBreaks(4) = 100
aBreaks(5) = 250
aBreaks(6) = 500
aBreaks(7) = 1000
aBreaks(8) = 2500
aBreaks(9) = 5000
aBreaks(10) = 12501

For i = 0 To 9
lQtyCnt = 0
For j = aBreaks(i) - 1 To ctl.Value = (aBreaks(i + 1)) - 1
If Left$(ctl.Name, 7) = "TextBox" Then
lQtyCnt = lQtyCnt + 1
If lQtyCnt <= 3 Then
For Each ctl In Me.Controls
Sheet1.Range("e1").Offset(76 + _
(lQtyCnt * 6),i).Value = (ctl)
Next ctl
End If
End If
Next j
Next i
End Sub

 

[Dick Kusleika]

You're using the control variable "ctl" outside of the For Each loop, which
you can't do.

 

[dan dungan]

Hi Dick,

Thanks for the feedback.

I'm getting closer.

I've got a few things I want to try before I give up and ask for your
help again.

Dan

 

[Dick Kusleika]

Hi Dick,

Thanks for the feedback.

I'm getting closer.

I've got a few things I want to try before I give up and ask for your
help again.

No problem. Ask when ready.

 

[dan dungan]

Hi Dick,

The code seems to produce the output I was looking for now.
So now I need to add functionality that will populate the delivery
lead time associated with each quantity.

To hold the delivery lead time values, I added 10 textboxes with the
naming convention: txtDelivery2, txtDelivery3, up to txtDelivery11.

8( for eight weeks), 7( for seven weeks), etc.

If there is a value in textbox2, there will be a corresponding value
in txtDelivery2.

If Quantity falls in the first price break at cell E83, the
corresponding delivery time needs to be in cell E86

There is a 3 cells each for quantity and delivery in each price
break.

I don't understand the logic I would need for this.

What I've done here never picks up the delivery value in the
txtDelivery control. The quantity portion still returns the proper
value.

Thanks for any comments.

Dan
______________________________________________________________
Private Sub CommandButton1_Click()
Dim vaBreaks As Variant
Dim i As Long
Dim ctl As Control 'Returns the value held in the quantity
textboxes
Dim j As Long 'Determine where the textbox value falls in the
price breaks
Dim lQtyCnt As Long 'to check how many quantities for each aBreak.
No more than 3 quanitities of each price break.

vaBreaks = VBA.Array(1, 10, 20, 50, 100, 250, 500, 1000, 2500,
5000, 12501)

For i = 0 To 9
lQtyCnt = 0
For j = vaBreaks(i) To vaBreaks(i + 1) - 1
For Each ctl In Me.Controls
If Left$(ctl.Name, 7) = "TextBox" Then
If ctl = j Then
lQtyCnt = lQtyCnt + 1
If lQtyCnt <= 3 Then
Sheet1.Range("e1").Offset(76 + (lQtyCnt *
6), i).Value = (ctl)
End If
If Left$(ctl.Name, 11) = "txtDelivery" Then
Sheet1.Range("e1").Offset(76 + (lQtyCnt *
9), i).Value = (ctl)
End If
End If
End If
Next ctl
Next j
Next i
End Sub

 

[dan dungan]

Hi Dick,

The code seems to produce the output I was looking for now.
So now I need to add functionality that will populate the delivery
lead time associated with each quantity.

To hold the delivery lead time values, I added 10 textboxes with the
naming convention: txtDelivery2, txtDelivery3, up to txtDelivery11.

8( for eight weeks), 7( for seven weeks), etc.

If there is a value in textbox2, there will be a corresponding value
in txtDelivery2.

If Quantity falls in the first price break at cell E83, the
corresponding delivery time needs to be in cell E86

There is a 3 cells each for quantity and delivery in each price
break.

I don't understand the logic I would need for this.

What I've done here never picks up the delivery value in the
txtDelivery control. The quantity portion still returns the proper
value.

Thanks for any comments.

Dan
______________________________________________________________
Private Sub CommandButton1_Click()
Dim vaBreaks As Variant
Dim i As Long
Dim ctl As Control 'Returns the value held in the quantity
textboxes
Dim j As Long 'Determine where the textbox value falls in the
price breaks
Dim lQtyCnt As Long 'to check how many quantities for each aBreak.
No more than 3 quanitities of each price break.

vaBreaks = VBA.Array(1, 10, 20, 50, 100, 250, 500, 1000, 2500,
5000, 12501)

For i = 0 To 9
lQtyCnt = 0
For j = vaBreaks(i) To vaBreaks(i + 1) - 1
For Each ctl In Me.Controls
If Left$(ctl.Name, 7) = "TextBox" Then
If ctl = j Then
lQtyCnt = lQtyCnt + 1
If lQtyCnt <= 3 Then
Sheet1.Range("e1").Offset(76 + (lQtyCnt *
6), i).Value = (ctl)
End If
If Left$(ctl.Name, 11) = "txtDelivery" Then
Sheet1.Range("e1").Offset(76 + (lQtyCnt *
9), i).Value = (ctl)
End If
End If
End If
Next ctl
Next j
Next i
End Sub