I would change the order of things and not use the windows collection.

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")

becomes:

Set ws = workbooks(cstrdatabasewb).workSheets("DCI data")

' Make the collection
On Error Resume Next
For Each cell In ws.Range("$B$6:$B$1696")

And at the end when you're populating the listbox:

For Each Item In NoDupes
lstFrom.AddItem format(Item, "mmmm dd, yyyy")
Next Item

(since you're working with dates)


and I'd qualify that lstfrom listbox, too. Maybe something like:


For Each Item In NoDupes
thisworkbook.worksheets("somesheetname").lstFrom.A ddItem _
format(Item, "mmmm dd, yyyy")
Next Item




Merlynsdad wrote:

I added a tab to Workbook B, inserted Listbox1, attached the following code
to the listbox, and it works like a charm. Then I put a line in workbook_open
which calls Sub RemoveDuplicates(), which has the exact same code as is
attached to Listbox1, only I'm adding the collection to lstFrom instead of
Listbox1, and it isn't even assembling the collection! NoDupes never shows
any items in the Watch window. Here's the code for RemoveDuplicates that's
called by workbook_open:

Private Sub RemoveDuplicates()

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
lstFrom.AddItem Item
Next Item

End Sub

There's obviously a difference in the way this code works when attached to a
listbox versus when it's called by workbook_open, but I don't know what it is.

"Dave Peterson" wrote:

John Walkenbach shows how he
http://spreadsheetpage.com/index.php..._in_a_listbox/

Merlynsdad wrote:

I'm filling a listbox in Workbook B from data in Sheet1 of Workbook A. The
data is a list of dates, and adjacent columns in Sheet1 show names, team
numbers, etc. The dates represent days things happened. When the data comes
into my listbox now, I have duplicate dates, because that's what's on Sheet1.
On October 1st I may have data for Johnny Johns, Sally Sals, and Marty
Martin, and so October 1st shows up three times in the listbox. The listbox
is used to get information for a given date range (there are actually two
identical listboxes, one for the FROM date, one for the TO date). What I
would like is for October 1st to show up only once, followed the October 2nd
etc. When queried however, I need all the information for everybody who has
data on October 1st.

The current code is as follows:
Worksheets("QueryDate").lstFrom.ListFillRange = "'[real1.xls]DCI
data'!$b$6:$b$1696"

Any help will be greatly appreciated.

--

Dave Peterson
.


--

Dave Peterson