Solved

Nested For Loops to Look at 2 ListBoxs in VBA UserForm

Posted on 2012-12-31
9
363 Views
Last Modified: 2013-01-01
Hi All

Happy New Year.  I'm attempting to compare items from one listbox with those in another listbox (both on the same userform).

I appear to be using the wrong approach because ListBoxA stops passing values when I introduce a nested ForLoop to look at ListBoxB.  Code as follows:
For i = 0 To myForm.ListBoxA.ListCount - 1
  myForm.ListBoxA.ListIndex = i
  AItem = myForm.ListBoxA.Value
  MsgBox ("List A: " & AItem)
    
'    For j = 0 To myForm.ListBoxB.ListCount - 1
'    Next j
Next i

Open in new window


As soon as I uncomment the inner For-Next the MsgBox will not display AItem

This is most alarming!

Cheers, S
0
Comment
Question by:DrTribos
  • 5
  • 2
  • 2
9 Comments
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 50 total points
ID: 38733911
You need to use ItemData property.
For i = 0 To myForm.ListBoxA.ListCount - 1
  AItem = myForm.ListBoxA.Itemdata(i)
  MsgBox ("List A: " & AItem)
    
'    For j = 0 To myForm.ListBoxB.ListCount - 1
'    Next j
Next i

Open in new window

0
 
LVL 15

Author Comment

by:DrTribos
ID: 38733965
Thanks for your reply....  I get an error:

Compile Error:
Method or data member not found

I tried changing the i to a 1 for testing purposes (there are 3 items in my list so this should have worked...  I have not uncommented the other code.

Cheers,
0
 
LVL 15

Author Comment

by:DrTribos
ID: 38734024
Do I need to reDim AItem?  Currently string, also tried DataObject... really I'm just guessing :-(
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 29

Expert Comment

by:IrogSinta
ID: 38734030
Sorry, I just noticed you're doing this in MS Word.  I assumed you were in Access.  Can you upload a copy of this document?  Just remove any private information first.
0
 
LVL 15

Author Comment

by:DrTribos
ID: 38734087
Will clean for upload.  The other thing I noticed is that if I click in the listbox before running my macro it works perfectly.  BUT if I don't click (which would be easily anticipated for this application) it does not work... is there a way to give focus to the list - that might help too...

BTW sorry for reply lag... for some reason my EE emails are not popping up :-(

Thanks
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 450 total points
ID: 38734190
Steve,
There seems to be a bug, but it isn't anything to do with the inner loop, at least on my system, where it happens on alternate runs. It fails; but stop VB and restart, and then it works.
IrogSinta has the right idea, but you need to use the List property
Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer
Dim AItem As String

For i = 0 To myForm.ListBoxA.ListCount - 1
  AItem = myForm.ListBoxA.List(i)
  MsgBox ("List A: " & AItem)
    
    'For j = 0 To myForm.ListBoxB.ListCount - 1
    'Next j
Next i
End Sub

Open in new window

.
0
 
LVL 15

Author Comment

by:DrTribos
ID: 38734371
Graham - works a treat, thanks.
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 450 total points
ID: 38734403
That's good news.
Incidentally, you could still use the .Value property by 'clicking' the listbox programatically.
Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer
Dim AItem As String

For i = 0 To myForm.ListBoxA.ListCount - 1
    myForm.ListBoxA.ListIndex = i
    myForm.ListBoxA.SetFocus
    AItem = myForm.ListBoxA.Value
    MsgBox ("List A: " & AItem)
    
    'For j = 0 To myForm.ListBoxB.ListCount - 1
    'Next j
Next i
End Sub

Open in new window

0
 
LVL 15

Author Comment

by:DrTribos
ID: 38734422
ahhh.... SetFocus is what I was looking for but did not know what to search for.  Thanks.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Always hangs on opening 8 89
Word ActiveX button becomes disconnected from template 9 67
Need to put a web footnote into a text book 7 33
Problem with MS Word 12 33
There is a feature provided by MS Word that lets you create an Table of Contents for your Word document automatically. To use this feature for other documents there are two steps involved,   1.  Prepare your document for a table of contents (he…
The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question