Re Post Please help: Listboxes question

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

Listbox1 =A0 =A0 Listbox2
XX =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AA
VV =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0XX
AA =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0SS

etc=85=85=85..

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.
0
K
3/18/2010 4:07:55 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
564 Views

Similar Articles

[PageSpeed] 15

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


0
K
3/18/2010 4:30:20 PM
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
0
Wouter
3/18/2010 8:52:34 PM
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
0
Dave
3/18/2010 11:09:35 PM
Thanks lot guys.  it works superb

0
K
3/19/2010 11:01:45 AM
Reply:

Similar Artilces:

Excel 97 VBA Help File
In the MS Excel Visual Basic Reference help file contents page, I click on Functions and it only offers me functions beginning with the letter S. So, I have a list of Solver and SQL functions. But what about all the other functions in VBA, for example for doing arithmetic and manipulating dates and strings? Why don't they show up? Are they left out because those functions are all part of Visual Basic generally, and the Excel VBA help file is specific to the _extra_ functions in Excel VBA? It's the only explanation I can think of. Am I right, or have I got a corrupted help file (vbaxl...

asap utilities question
I have Msft. Excel Vsn 2002 and latest vsn. of ASAP Utilities. I used ASAP Utilities to merge single rows of data together so that more than one row will fit on a single line of text. My question now, is, is it possible using ASAP Utilities to separate the data that has been merged back into single rows of data? All information welcomed! Aaron ...

Isinteg Warning Message Question
I ran isinteg -pri -test alltests. I came up with 506 warnings. There are basically 4 types of warnings. They are below. Are these something I need to worry about? What should I do? thanks stewart sschwartz@nal.usda.gov ================================== Warning: MsgFolder 165 (Fid=0001-0000000D1864, Mid=0001- 0000000D559D, Inid=0001-000000F33436): PR_READ_RECEIPT_REQUESTED(0029000B) prop in Messages table and MsgFolder table do not match. Warning: MsgFolder 4 (Fid=0001-0000086DC911, Mid=0001- 00000436F972, Inid=0001-000004A583F1): Error JET_errRecordNotFound seeking to INID for this ...

help need with VC 6.0 IDE and mfc
Hello, First let me explain the scenario where i m using this requirement. We are Using CustomAppWizard and designing a wizard .One of the wizard pages will Insert Composite controls as many as the user wants . 1.So i should be able to dynamically insert ATL controls without using Insert Control Dailog. 2. can any one tell me how to dynamically create Template file in TEMPLATE folder of resource view . 3. I want to include many files created by templet files and add them to build by editing newproj.inf Is it possible to do this. 4.I would even like to know if i have 2 ifles in my C drive h...

Help, I cannot Save!
I created a document and locked the worksheet to protect the formulars before creating a template for the document. But now when I open th document and insert a new sheet using the template I created, th document will refuse to save. Once I click on save, office assistant will say "doc not saved". Wha could I have done wrong? PLease help. computerfinema -- computerfinema ----------------------------------------------------------------------- computerfineman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3716 View this thread: http://www.excelforum.c...

Help Required
Hi, Whenever I open Outlook 2003, I am getting a dialog box which displays the following message: Microsoft Office Outlook has encountered a problem and needs to close. We are sorry for the inconvenience When I click Debug it displays a message box with the following error message "The instruction at "0x3007e993" referenced memory at "0x0000000:. The memory could not be read" When I click No it Visual Studio JIT debugger pops up. I uninstalled and installed several times but still the problem persists. Is there any regsitry entry that I've to modify/delete? ...

Need Help with Deleting Empty Paragraphs in Word 2003
I have written the code below to delete all empty paragraphs at the end of a document and then place the cursor at the end of the last paragraph. It works fine as a stand alone sub in a new doc, but fails inside the real document that contains other code that manipulates several documents. The failure is that it will delete the last empty para, but then gets stuck looping inside the While...Wend because subsequent .Delete are not happening. So, the question is why would this work in one document, but then fail in another? n = 0 ...

HELP Recovering addresses and email from Outlook 2003
I had some serious driver issues that required re-installing XP from disc. I did use the backup option and have a backup of all the old data. And of course had to reinstall Office 2003. Will third party software restore my old email and addresses or am I out of luck?? Thanks for the help texraid wrote: > I had some serious driver issues that required re-installing XP from > disc. I did use the backup option and have a backup of all the old > data. And of course had to reinstall Office 2003. > > Will third party software restore my old email and addresses or am I > out of lu...

Dotfuscator question
Hello Everybody !!! When i create programm using C# i must understand that my programm from exe - module can be converted to the source file by Reflector. In Order do not allow do this i must use dotfuscator, but i did not find any free normal dotfuscator. May be someone tell me what can i do cause now as think we have interesting paradox. We 've got free technology but if we want to protect our products we must buy expensive commercial dotfuscators. Alex Dmitriev You should post this message in a newsgroup which discusses managed code. This newsgroup does not have much managed traff...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

question on the rules wizard
When clikcing on the Rules Wizard, Outlook locks up - consistently. Has anyone got a remedy for this? Thank you, rich rpage@concerto.com ...

Re: 'Uknown Error 0x800CCC97'
I just heard back from the folks with whom I filed this bug. They say the bug is fixed in cppop 5.4 - request that your ISP upgrade to that. -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "Jeff Stephenson [MSFT]" <stephenson@online.microsoft.com> wrote in message news:... > See the attached reply to another similar question. Your ISP's POP3 server > has a bug, and they should get a fixed version of the server. > > -- > Jeff Stephenson > Outlook Development > This posting...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

OE questions
Ok, I have two questions: 1. With Thunderbird I can use this with a hotkey manager to compose a new message with Tbird "D:\Program Files\Mozilla Thunderbird \thunderbird.exe -compose" -- how can I do the same for Outlook Express and Outlook 2003? 2. When I go to load Outlook Express I get this error message, followed by the error message below it: http://i180.photobucket.com/albums/x296/gwar_1/Untitled-3.png how can I fix this and use Outlook Express? Thanks! posted to the outlookexpress newsgroup via crosspost --=20 Peter Please Reply to Newsgroup for the benefit of others Re...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

Help with Registration
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, I've tried to register my copy of Office for Mac through the Mactopia page. I log in successfully, but then it just keeps on loading and doesn't refresh or change. Any advice? On 6/16/09 6:35 PM, in article 59b76b64.-1@webcrossing.caR9absDaxw, "theconfuzed1@officeformac.com" <theconfuzed1@officeformac.com> wrote: > I've tried to register my copy of Office for Mac through the Mactopia page. I > log in successfully, but then it just keeps on loading and doesn't refresh or > ...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

HELP! Outlook POP3 problem(s)
Hello. I am so lost. I have a few e-mail accounts set up on my computer which retrieves my mail from a couple of different providers and deposits the mail into my Outlook Inbox. Up until yesterday, my mail always has worked fine. For some strange reason, my Outlook is now (Again) retrieving my messages from all of my accounts I had set up, which are all duplicates of my messages. There is now nearly 4,000 duplicate messages in my folders. I can't seem to stop the download of these already retrieved messages. To top things off, a couple of my email account login windows keep p...

HQ Licensing question V.2
I have a client running HQ client at 8 stores + HQ server at the warehouse. They would like to be able to use transfers from the warehouse to the stores, so I would have to setup a new PC running Store Ops and a new database, however, in order for the transfers to take place, I need a HQ Client License correct? Do I also need a POS key? I appreciate any insights you may have. Thanks in advance Hi Phil yes - you'll need an HQ Client license so it can exchange with your headquarters - "export" the wh database as you would any store from HQ no - you won't need a PO...

Spam Filtering HELP
I recently started a new job, and discovered after day one, that I had inharited a spam mess. Now the previous admin ad installed a Symantic Spam Server Prox which in my opinion, was a complete waste of money as it does not allow for blocking IP addresses. Now here is the question; I am running Exchange 2003, and am looking at setting up the Conection Filter under Message Delivery to block messages based on IP address. The problem is that when I save the IPs to be blocked, I get a message stating that the Connection filter "has to be enabled manually through the specific SMTP virtual serv...

Need macro help to close excel
I have created a button in Access2000 that opens an Excel Spreadsheet. What I need now is assit in closing excel upon completion. I can get an excel macro to save my file and close the worksheet, but it is not closing excel entirely. I'm on project with this employer and could use a response today to fix this before I leave. Thanks much to any and all. My macro is as follows: Sub SaveClose() ' ' SaveClose Macro ' Macro recorded 9/27/2004 by cdjohnso ' ' Keyboard Shortcut: Ctrl+Shift+C ' ChDir "I:\SchoolsSurvey\Graphs_Reports" ActiveWorkb...

Help with simple(?) VBA function
I'm trying to selectively BOLD cells by the use of a User-Defined function. No joy. The VBA Help topics suggest something like this: Function Bold() Worksheets("Sheet1").Range("A1:A5").Font.Bold = True End Function When I try to use it the referenced cells are not changed and the function returns "0". Can anyone point this VBA neophyte in the right direction? Thanks, -Dick- Hi Dick, A function can only return a value. Macros and Functions (Macros as Opposed to Functions) http://www.cpearson.com/excel/differen.htm If all...

test post
please delete this post ...

If Then Help!!!!
Hi! I'm stuck. I have a working macro but it needs a small tweek. The macro executes a find statement and performs calculations from the find to the end of the column. The problem is when nothing is found. I need an if statement or suggestion on how to tell it to skip the calculations if there is nothing found. This is what I have so far(with no if's): Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Selection.End(xlToRi...

MS Project 2007 question
Hello, I am developing a simple schedule with no resources but 2 tasks out of 100 are hazardous and I would like to make the schedule so that when these tasks occur, no other tasks can occur. In other words, even though all tasks are moving around as the schedule is being developed, I would like to make sure no other tasks can be scheduled wherever these 2 hazardous tasks fall. These 2 tasks are serial to every other task in the schedule. Is there a way to do this? Thanks for your help. Mike Create two milestones. Use milestone 1 as the successor task to all tasks th...