Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile

    I have a listbox in Workbook A on Worksheet QueryDate that calls data from Workbook B. It is run from the workbook_open module in Workbook A. The data in the listbox consists of dates that things happened. In Workbook B adjacent to the dates are names, teams, etc. There are actually two identical listboxes, lstTO and lstFROM so the user can choose a date range. The problem is, there is more than one item for each date, so the listboxes have duplicate dates in them. To rectify this problem so that I have only unique dates, I tried using the following code:

    Private Sub RemoveDuplicates1()

    Dim AllCells As Range, cell As Range
    Dim NoDupes As New Collection
    Dim ws As Worksheet
    Const cstrDatabaseWB As String = "real1.xls"

    On Error Resume Next

    Windows(cstrDatabaseWB).Activate
    Set ws = Sheets("DCI data")

    ' Make the collection

    For Each cell In ws.Range("$B$6:$B$1696")
    NoDupes.Add cell.Value, CStr(cell.Value)
    Next cell

    On Error GoTo 0

    ' Add the collection to the listbox

    For Each Item In NoDupes
    ListBox1.AddItem Item
    Next Item

    End Sub

    When I inserted an extra worksheet in Workbook A and put a listbox on it, this code worked just fine attached to the listbox. However, when I use the code in the workbook_open module, it doesn't even generate values in NoDupe. How do I need to modify this code to get it to do what I want? Any help is always appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    When RemoveDuplicate is in the worksheet module of QueryDate, ListBox1 refers to a list box on QueryDate.
    But when you place a copy of this procedure in the Workbook_Open module, it is not clear any more what ListBox1 refers to. You might try changing the line

    ListBox1.AddItem Item

    to

    Me.Worksheets("QueryDate").ListBox1.AddItem Item

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='800470' date='29-Oct-2009 02:36']When RemoveDuplicate is in the worksheet module of QueryDate, ListBox1 refers to a list box on QueryDate.
    But when you place a copy of this procedure in the Workbook_Open module, it is not clear any more what ListBox1 refers to. You might try changing the line

    ListBox1.AddItem Item

    to

    Me.Worksheets("QueryDate").ListBox1.AddItem Item[/quote]

    Thanks, I'll give that a try. But why wouldn't NoDupes even show it was assembling the collection in the Watch window? Shouldn't that happen whether it knows where to add them or not? NoDupes stayed empty throughout the entire For Each section.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Perhaps ws wasn't pointing to the correct sheet.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='800491' date='29-Oct-2009 08:00']Perhaps ws wasn't pointing to the correct sheet.[/quote]
    When I inserted an additional sheet in the workbook and put a listbox on it, I used this same code and it worked fine. All the unique dates loaded. Now I'm calling this module from workbook_open to fill listboxes in QueryDate, so the only difference should be the coding of the location of the listbox. However, when I call it and it runs, it's not even assembling the collection. NoDupes stays empty. All the code, including the source of the data, is the same except for the listbox location.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What happens if you change the lines

    Windows(cstrDatabaseWB).Activate
    Set ws = Sheets("DCI data")

    to

    Set ws = Workbooks(cstrDatabaseWB).Sheets("DCI data")

  7. #7
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    What happens if you change the lines

    Windows(cstrDatabaseWB).Activate
    Set ws = Sheets("DCI data")

    to

    Set ws = Workbooks(cstrDatabaseWB).Sheets("DCI data")
    It works like a charm! Nice new look. Congrats.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •