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

[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).L istCount-1
if asc(left(ListBo x(lstb).List(nd x),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

[Killer42]

for lstb=0 to 7
for ndx=0 to ListBox(lstb).L istCount-1
if asc(left(ListBo x(lstb).List(nd x),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).L ist(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.

[Killer42]

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

[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.

' Find first non-numerical chr in each line of listbox.
' Assumptions:
'   1) listboxes are in an array ListBox(0 to 7)
'   2) Maximum entries in any listbox: 100

' lstb is counter for listboxes
' ndx is counter for line within listbox
' char is count for charater within line

' Array to hold first non-numeric char for each line in each
' listbox...
Dim FirstChar(0 To 7, 1 To 100) As String

Dim lstb As Long, ndx As Long, chnum As Long
Dim Char As String
Dim MaxLine As Long


For lstb = 0 To 7
  With ListBox(lstb)
    If .ListCount > MaxLines Then
      MaxLines = .ListCount
    End If
    For ndx = 0 To .ListCount - 1
      For chnum = 1 To Len(.List(ndx))
        Char = Mid$(.List(ndx), Char, 1)
        Select Case Char
          Case "0" To "9"
            ' Ignore
          Case Else
            FirstChar(lstb, ndx) = Char
            Exit For
        End Select
      Next
    End With
  Next
Next

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.

[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.

' Find first non-numerical chr in each line of listbox.
' Assumptions:
'   1) listboxes are in an array ListBox(0 to 7)
'   2) Maximum entries in any listbox: 100

' lstb is counter for listboxes
' ndx is counter for line within listbox
' char is count for charater within line

' Array to hold first non-numeric char for each line in each
' listbox...
Dim FirstChar(0 To 7, 1 To 100) As String

Dim lstb As Long, ndx As Long, chnum As Long
Dim Char As String
Dim MaxLine As Long


For lstb = 0 To 7
  With ListBox(lstb)
    If .ListCount > MaxLines Then
      MaxLines = .ListCount
    End If
    For ndx = 0 To .ListCount - 1
      For chnum = 1 To Len(.List(ndx))
        Char = Mid$(.List(ndx), Char, 1)
        Select Case Char
          Case "0" To "9"
            ' Ignore
          Case Else
            FirstChar(lstb, ndx) = Char
            Exit For
        End Select
      Next
    End With
  Next
Next

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).listc ount
for i=1 to len(lstbox(a).l ist(b))
c=mid(lstbox(a) .list(b),i,1)
n=instr(1,"0123 456789",c)
if n=0 then
lb(a,b)=c
exit for
endif
next i
next b
next a

then compare diff lb's

[Killer42]

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

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).

[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

[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))

[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.

[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 misunderstandin g between VB6 and VBA and have managed to straighten most of it out there might be a few problems left firstly in the following:

    For Each Thing In UserForm1.Controls      ' thing is a control
    If TypeName(Thing) = "ListBox" Then
    For lstb = 1 To 8
    With Thing
    For ndx = 0 To .ListCount - 1
      For chnum = 1 To Len(.List(ndx))
        Char = Mid$(.List(ndx), Char, 1)
        Select Case Char
          Case "0" To "9"
            ' Ignore
          Case Else
            FirstChar(lstb, ndx) = Char
            Exit For
        End Select
      Next
  Next
  End With
Next
End If
Next Thing

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?

[albertw]

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

    For Each Thing In UserForm1.Controls      ' thing is a control
    If TypeName(Thing) = "ListBox" Then
    For lstb = 1 To 8
    With Thing
    For ndx = 0 To .ListCount - 1
      For chnum = 1 To Len(.List(ndx))
        Char = Mid$(.List(ndx), Char, 1)
        Select Case Char
          Case "0" To "9"
            ' Ignore
          Case Else
            FirstChar(lstb, ndx) = Char
            Exit For
        End Select
      Next
  Next
  End With
Next
End If
Next Thing

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

[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.

[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

[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.)