Solved

Nested For Loops to Look at 2 ListBoxs in VBA UserForm

Posted on 2012-12-31
9
362 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

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…
If you work with Word a lot, you probably use styles. If you use styles a lot, you've probably balled your fist more often than not when working with the ribbon. In Word 2007/2010, one of the things that I find missing when using styles is a quic…
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

776 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