Solved

Nested For Loops to Look at 2 ListBoxs in VBA UserForm

Posted on 2012-12-31
9
360 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 14

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 14

Author Comment

by:DrTribos
ID: 38734024
Do I need to reDim AItem?  Currently string, also tried DataObject... really I'm just guessing :-(
0
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

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 14

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 14

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS PAint how to type 14 124
PHP Word Writer 1 45
Word 2010 mail merge 3 37
Prevent user closing word document opened with VB6 6 27
Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
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 video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now