Re Post Please help: Listboxes question

[K]

Hi all,  I got two listboxes on a spreadsheet and in those listboxes I
got data like (see below)

Listbox1     Listbox2
XX                AA
VV                XX
AA                SS

etc………..

I need macro on a button which should match listbox1 items with
listbox2 items and then show both listbox items one by one on a
messagebox , but keeping in mind that matched items should be shown
only one time in messabebox

Please can any frined help me on this.

 

[K]

I was able to show matched items once on a messagbox with below macro
but i dont know that how to show un matched items of both listboxes
one by one on messagebox

Private Sub CommandButton1_Click()
For L1 = 0 To Me.ListBox1.ListCount - 1
For L2 = 0 To Me.ListBox2.ListCount - 1
LB1 = Me.ListBox1.List(L1)
LB2 = Me.ListBox2.List(L2)
If LB1 = LB2 Then
MsgBox LB1
End If
Next
Next
End Sub

 

[Wouter HM]

Hi K,

In Excel 2007 I created this:

Private Sub CommandButton1_Click()
Dim L1 As Integer
Dim L2 As Integer
Dim LB1 As String
Dim LB2 As String
ReDim UM1(Me.ListBox1.ListCount - 1) As String
ReDim UM2(Me.ListBox2.ListCount - 1) As String

For L1 = 0 To Me.ListBox1.ListCount - 1
UM1(L1) = Me.ListBox1.List(L1)
Next
For L2 = 0 To Me.ListBox2.ListCount - 1
UM2(L2) = Me.ListBox2.List(L2)
Next

For L1 = 0 To Me.ListBox1.ListCount - 1
LB1 = Me.ListBox1.List(L1)
For L2 = 0 To Me.ListBox2.ListCount - 1
LB2 = Me.ListBox2.List(L2)
If LB1 = LB2 Then
MsgBox LB1, , "Matched"
UM1(L1) = ""
UM2(L2) = ""
End If
Next
Next

For L1 = 0 To Me.ListBox1.ListCount - 1
If UM1(L1) <> "" Then
MsgBox UM1(L1), , "listbox 1 Unmatched"
End If
Next

For L2 = 0 To Me.ListBox2.ListCount - 1
If UM2(L2) <> "" Then
MsgBox UM2(L2), , "listbox 2 Unmached"
End If
Next

End Sub


HTH,

Wouter

 

[Dave Peterson]

Another way:

Option Explicit
Private Sub CommandButton1_Click()

Dim myArr1() As String 'Listbox1 entries
Dim myArr2() As String 'Listbox2 entries

Dim res As Variant 'could be an error
Dim iCtr As Long

'transfer the items in listbox1 into the array
With Me.ListBox1
ReDim myArr1(0 To .ListCount - 1)
For iCtr = 0 To .ListCount - 1
myArr1(iCtr) = .List(iCtr)
Next iCtr
End With

'transfer the items in listbox2 into the array
With Me.ListBox2
ReDim myArr2(0 To .ListCount - 1)
For iCtr = 0 To .ListCount - 1
myArr2(iCtr) = .List(iCtr)
Next iCtr
End With

'loop through the items in listbox1/myArr1 looking
'for matches in listbox2/myArr2
For iCtr = LBound(myArr1) To UBound(myArr1)
res = Application.Match(myArr1(iCtr), myArr2, 0)
If IsError(res) Then
'not found
MsgBox myArr1(iCtr) & vbLf & "wasn't found in LB2"
End If
Next iCtr

'loop through the items in listbox2/myArr2 looking
'for matches in listbox1/myArr1
For iCtr = LBound(myArr2) To UBound(myArr2)
res = Application.Match(myArr2(iCtr), myArr1, 0)
If IsError(res) Then
'not found
MsgBox myArr2(iCtr) & vbLf & "wasn't found in LB1"
End If
Next iCtr

End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long
For iCtr = 1 To 3
Me.ListBox1.AddItem "A" & iCtr
Me.ListBox1.AddItem "B" & iCtr
Me.ListBox2.AddItem "A" & iCtr
Me.ListBox2.AddItem "D" & iCtr
Next iCtr
End Sub

 

[K]

Thanks lot guys. it works superb