It's not a workbook level permission.

I'd guess that you still have the .ListFillRange set (either manually in the
properties window or that line is still in your code).

If the line is still in your code, remove it.
If the .ListFillRange was manually changed in the properties window, empty that
setting.

Another option is to just change it before you try to use .additem.

with me.worksheets("QueryData").listfrom
.clear
.listfillrange = ""
end with

'then do the .additem

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

Merlynsdad wrote:

The "set=ws" line you suggested works fine, and it's now assembling the
collection. However, I'm getting a very strange error "Run Time 70 Permission
Denied" on the following line:

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

There shouldn't be any permissions involved as this is in the same workbook,
on my C: drive, and I wrote the worksheet. I could previously populate this
listbox without any problems but it had duplicate entries in it. Substituting
"Thisworkbook" for the "Me" doesn't help. I've never run across this error
before, and the VB HELP item doesn't help. This code is in a module being
called by workbook_open if that gives you any clues.

"Dave Peterson" wrote:

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
.


--

Dave Peterson