July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Nested For Loops to Look at 2 ListBoxs in VBA UserForm
Posted on 2012-12-31
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:
As soon as I uncomment the inner For-Next the MsgBox will not display AItem
This is most alarming!
Cheers, S
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
As soon as I uncomment the inner For-Next the MsgBox will not display AItem
This is most alarming!
Cheers, S
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
2
2
9 Comments
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
Active 7 days ago
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,
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,
Active 7 days ago
Do I need to reDim AItem? Currently string, also tried DataObject... really I'm just guessing :-(
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.
Active 7 days ago
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
BTW sorry for reply lag... for some reason my EE emails are not popping up :-(
Thanks
Active today
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
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
Active today
That's good news.
Incidentally, you could still use the .Value property by 'clicking' the listbox programatically.
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
Featured Post
With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Using Word 2013, I was experiencing some incredible lag when typing. Here's what worked for me....
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
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.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 12 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.