VBA listbox help

Blckbx

I have 8 list boxes and i need to compare values stored in them, i need to find the first non-numerical character in each line in each listbox and then compare between listboxes does anyone have any ides how to do this?

cheers

Black box

Nov 13 '06 #1

albertw

I have 8 list boxes and i need to compare values stored in them, i need to find the first non-numerical character in each line in each listbox and then compare between listboxes does anyone have any ides how to do this?

cheers

Black box

hi

dim lstbLine(7)

'find first non-numerical chr is line of listbox and remember
'assumed listboxes are put into an array
'lstb is counter for listboxes
'ndx is counter for non-numerical line

for lstb=0 to 7
for ndx=0 to ListBox(lstb).ListCount-1
if asc(left(ListBox(lstb).List(ndx),1)) > 64 then
lstbLine(lstb)=ndx
exit for
end if
next ndx
next lstb

'compare different listbox lines
for lstb=0 to 6
for lstb1=lstb+1 to 7
if lstbLine(lstb)=lstbLine(lstb1) then
msgbox "Listbox " & lstb & " has same value as Listbox " & lstb1
end if
next lstb1
next lstb

Nov 13 '06 #2

Killer42

for lstb=0 to 7
for ndx=0 to ListBox(lstb).ListCount-1
if asc(left(ListBox(lstb).List(ndx),1)) > 64 then
lstbLine(lstb)=ndx
exit for
end if
next ndx
next lstb

This doesn't actually match the spec. The request was to find the "first non-numeric character in each line". I believe this code will find the first line which starts with a non-numneric. You might actually need to use a third nested loop to run through each character in ListBox(lstb).List(ndx), and do something with the character you find, then Exit For.

Just in case you're interested, the Left() function in the above code is unnecessary; the ASC() function will only return the ASCII value of the first character anyway.

Nov 13 '06 #3

Killer42

P.S. Sorry if I sounded somewhat brusque, just very busy at work right now.

Nov 13 '06 #4

Killer42

Ok, I've had time to go over this one a little. Here's the code I've come up with - I've just expanded a little on albertw's code. Note that while this was written in VB6, it has not been tested.
In theory, this should leave you with a two-dimensional array (listboxnum, linenum) giving the character which was found on each line of each listbox. Variable MaxLines should contain the number of lines in the longest list.

I don't know exactly what sort of comparison you want to do between them, but albertw's routine should give you a good base to work from.

Nov 14 '06 #5

albertw

Ok, I've had time to go over this one a little. Here's the code I've come up with - I've just expanded a little on albertw's code. Note that while this was written in VB6, it has not been tested.

Expand|Select|Wrap|Line Numbers

1. ' Find first non-numerical chr in each line of listbox.
2. ' Assumptions:
3. '   1) listboxes are in an array ListBox(0 to 7)
4. '   2) Maximum entries in any listbox: 100
5.  
6. ' lstb is counter for listboxes
7. ' ndx is counter for line within listbox
8. ' char is count for charater within line
9.  
10. ' Array to hold first non-numeric char for each line in each
11. ' listbox...
12. Dim FirstChar(0 To 7, 1 To 100) As String
13.  
14. Dim lstb As Long, ndx As Long, chnum As Long
15. Dim Char As String
16. Dim MaxLine As Long
17.  
18.  
19. For lstb = 0 To 7
20.   With ListBox(lstb)
21.     If .ListCount > MaxLines Then
22.       MaxLines = .ListCount
23.     End If
24.     For ndx = 0 To .ListCount - 1
25.       For chnum = 1 To Len(.List(ndx))
26.         Char = Mid$(.List(ndx), Char, 1)
27.         Select Case Char
28.           Case "0" To "9"
29.             ' Ignore
30.           Case Else
31.             FirstChar(lstb, ndx) = Char
32.             Exit For
33.         End Select
34.       Next
35.     End With
36.   Next
37. Next
38.  

In theory, this should leave you with a two-dimensional array (listboxnum, linenum) giving the character which was found on each line of each listbox. Variable MaxLines should contain the number of lines in the longest list.

I don't know exactly what sort of comparison you want to do between them, but albertw's routine should give you a good base to work from.

hi

agree to create a 2dim array

dim lb(num,lin)

for a=0 to 7
for b=1 to lstbox(a).listcount
for i=1 to len(lstbox(a).list(b))
c=mid(lstbox(a).list(b),i,1)
n=instr(1,"0123456789",c)
if n=0 then
lb(a,b)=c
exit for
endif
next i
next b
next a

then compare diff lb's

Nov 14 '06 #6

Killer42

hi

agree to create a 2dim array

Expand|Select|Wrap|Line Numbers

1. dim lb(num,lin)
2.  
3. for a=0 to 7
4.    for b=1 to lstbox(a).listcount
5.      for i=1 to len(lstbox(a).list(b))
6.      c=mid(lstbox(a).list(b),i,1)
7.      n=instr(1,"0123456789",c)
8.      if n=0 then
9.      lb(a,b)=c
10.      exit for
11.      endif
12.      next i
13.    next b
14. next a

then compare diff lb's

I prefer my version :)

Seriously, you forgot about the zero-base on the "b" loop. You'll get a bad-index error (I forget the specifics, been working in other languages too much).

Nov 14 '06 #7

albertw

I prefer my version :)

Seriously, you forgot about the zero-base on the "b" loop. You'll get a bad-index error (I forget the specifics, been working in other languages too much).

yes yr right k
b should start at 0

Nov 14 '06 #8

Blckbx

hate to point it out that my VB doesn't like either of your responses, though they look as if they work in spectacular form it likes neither lstbox(a) (or ListBox(a)) or ListBox(lstb) (or lstbox(lstb))

Nov 14 '06 #9

Killer42

hate to point it out that my VB doesn't like either of your responses, though they look as if they work in spectacular form it likes neither lstbox(a) (or ListBox(a)) or ListBox(lstb) (or lstbox(lstb))

Well, you have to keep in mind that we are making assumptions and you need to adjust for them. For instance, we have assumed that your listboxes are in an array. This is certainly the most convenient way to deal with them, as you can use a loop to run through them, as albertw and I have been demonstrating.

If they are not an array, then the coding gets rather awkward, as you need to explicitly reference each one. At the simplest level, this may mean writing the same code 8 times, though there are ways you might get around it.

Also, even if they are a control array, you have to use the right name. We just assumed a name of lstbx, or ListBox, or whatever. You need to use your name here, not ours.

If you're not sure, making a control array involves giving controls (of the same type) the same name, and a value in the Index property. If you simply copy a control and paste it back into the same form, it will ask whether you want to make it an array, and if you say yes, will give the original one an Index value of 0. The pasted one will have Index = 1, and so on.

Nov 14 '06 #10

Blckbx

Well, you have to keep in mind that we are making assumptions and you need to adjust for them. For instance, we have assumed that your listboxes are in an array. This is certainly the most convenient way to deal with them, as you can use a loop to run through them, as albertw and I have been demonstrating.

If they are not an array, then the coding gets rather awkward, as you need to explicitly reference each one. At the simplest level, this may mean writing the same code 8 times, though there are ways you might get around it.

Also, even if they are a control array, you have to use the right name. We just assumed a name of lstbx, or ListBox, or whatever. You need to use your name here, not ours.

If you're not sure, making a control array involves giving controls (of the same type) the same name, and a value in the Index property. If you simply copy a control and paste it back into the same form, it will ask whether you want to make it an array, and if you say yes, will give the original one an Index value of 0. The pasted one will have Index = 1, and so on.

sorry if i sounded a little ungrateful on my last post i was a little busy, i think the problem is a misunderstanding between VB6 and VBA and have managed to straighten most of it out there might be a few problems left firstly in the following:

when it reaches "For ndx = 0 To .ListCount - 1" it skips straight down to "end with" why would it do this? and how can i stop it?

Nov 15 '06 #11

albertw

sorry if i sounded a little ungrateful on my last post i was a little busy, i think the problem is a misunderstanding between VB6 and VBA and have managed to straighten most of it out there might be a few problems left firstly in the following:

Expand|Select|Wrap|Line Numbers

1.     For Each Thing In UserForm1.Controls      ' thing is a control
2.     If TypeName(Thing) = "ListBox" Then
3.     For lstb = 1 To 8
4.     With Thing
5.     For ndx = 0 To .ListCount - 1
6.       For chnum = 1 To Len(.List(ndx))
7.         Char = Mid$(.List(ndx), Char, 1)
8.         Select Case Char
9.           Case "0" To "9"
10.             ' Ignore
11.           Case Else
12.             FirstChar(lstb, ndx) = Char
13.             Exit For
14.         End Select
15.       Next
16.   Next
17.   End With
18. Next
19. End If
20. Next Thing
21.  

when it reaches "For ndx = 0 To .ListCount - 1" it skips straight down to "end with" why would it do this? and how can i stop it?

if there is nothing in the "thing" list, the loop will stop directly

Nov 15 '06 #12

Blckbx

if there is nothing in the "thing" list, the loop will stop directly

the "Thing list" isn't the problem it's the "For ndx = 0 To .ListCount - 1" line. When stepping through the code, it'll run the "with Thing" line, highlight the "For ndx = 0 To .ListCount - 1" and skip straight to "end with" on the next step. no error message it just runs through that same process for each lstb value.

Nov 15 '06 #13

albertw

the "Thing list" isn't the problem it's the "For ndx = 0 To .ListCount - 1" line. When stepping through the code, it'll run the "with Thing" line, highlight the "For ndx = 0 To .ListCount - 1" and skip straight to "end with" on the next step. no error message it just runs through that same process for each lstb value.

hi
just what i meant
if the thing.listcount=0 then the procedure will be skipped

Nov 15 '06 #14

Killer42

the "Thing list" isn't the problem it's the "For ndx = 0 To .ListCount - 1" line. When stepping through the code, it'll run the "with Thing" line, highlight the "For ndx = 0 To .ListCount - 1" and skip straight to "end with" on the next step. no error message it just runs through that same process for each lstb value.

When it gets to the For statement as mentioned here, could you hover over the .ListCount and see what value it shows? (If hovering doesn't work, just select it and do a quick watch.)

Nov 15 '06 #15

Killer42

hi
just what i meant
if the thing.listcount=0 then the procedure will be skipped

Yes, as albertw points out, if the listcount is 0 the For statement will be set to loop from 0 to -1. In which case, we will never enter the loop.

Nov 15 '06 #16

Blckbx

Yes, as albertw points out, if the listcount is 0 the For statement will be set to loop from 0 to -1. In which case, we will never enter the loop.

my mistake i'd left in the userform1.show above the code, then closed it to run the code underneath and it'd reset the .listcount to 0. works now.

cheers

black box

Nov 16 '06 #17