Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re Post Please help: Listboxes question
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re Post Please help: Listboxes question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re Post Please help: Listboxes question
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re Post Please help: Listboxes question
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 wrote: 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re Post Please help: Listboxes question
Thanks lot guys. it works superb
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
*****still can't post a question***** | Excel Discussion (Misc queries) | |||
POST A QUESTION | Excel Discussion (Misc queries) | |||
Why can't I post a question? | Excel Discussion (Misc queries) | |||
A Question about Listboxes | Excel Programming | |||
One more question on ListBoxes (I hope) | Excel Programming |