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.

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

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
.

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

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
.

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

Thanks! Yeah, the ListFill Range in the listbox properties were set from a
previous incarnation of the program. That solved it, and it's working
perfectly now.

"Dave Peterson" wrote:

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
.

Now comes the time when you'll want to start tweaking the code to make it work
"more perfect".

Remember to save backups before you screw up, er, enhance the devil out of it!

Merlynsdad wrote:

Thanks! Yeah, the ListFill Range in the listbox properties were set from a
previous incarnation of the program. That solved it, and it's working
perfectly now.

"Dave Peterson" wrote:

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
.


--

Dave Peterson

I'm tweaking now using the real files and have run into another problem,
actually two. In the msquery.xls workbook_open module I have code that
populates two other list boxes, Employee and Team. That code, when used with
a drive on my C:\ drive is as follows:

Const cstrDatabaseWB as String = "C:\Sherry project\real1.xls"
Application.workbooks.open(cstrDatabaseWB)
Me.Activate
Worksheets("QueryEmp").lstEmployee.ListFillRange = "[real1.xls]Lists!C2:C211"

That works fine.
However, the real path is H:\ACS QA\FY10 Reviews (version1).xls.
I've put that in the constant string, but as I understand it I don't need to
call the entire path once the file is open, just the filename. So I've Dim'd
a variable strFilename as String, and just above the Me.Activate command I've
inserted the line

strFilename = [FY10 Reviews (version1).xls]

Then in place of the "[real1.xls etc] in the ListFillRange I've put in
[strFilename]. It's not working, and I'm confused enough that I can't figure
out why.

The second problem is in what you solved yesterday, the collection. The line
Set ws=workbooks(cstrDatabaseWB).Sheets("DCI data")
works fine on the local drive, but not when the constant is defined with the
network drive.

Any help is greatly appreciated.


"Dave Peterson" wrote:

Now comes the time when you'll want to start tweaking the code to make it work
"more perfect".

Remember to save backups before you screw up, er, enhance the devil out of it!

Merlynsdad wrote:

Thanks! Yeah, the ListFill Range in the listbox properties were set from a
previous incarnation of the program. That solved it, and it's working
perfectly now.

"Dave Peterson" wrote:

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
.


--

Dave Peterson
.

I would still use variables to represent the objects I wanted to use.

dim myListRng as range
dim DBWB as workbook

const cstrdatabasewb as string = "whatever"

set dbwb = workbooks.open(cstrdatabasewb)

with dbwb.worksheets("Lists")
set mylistrng = .range("C2:C211")
end with

me.worksheets("queryemp").lstemployee.listfillrang e _
= mylistrng.address(external:=true)

I'd probably want to close that workbook, so I'd use:

me.worksheets("queryemp").lstemployee.list = mylistrng.value
dbwb.close savechanges:=false

I don't understand the second problem.




Merlynsdad wrote:

I'm tweaking now using the real files and have run into another problem,
actually two. In the msquery.xls workbook_open module I have code that
populates two other list boxes, Employee and Team. That code, when used with
a drive on my C:\ drive is as follows:

Const cstrDatabaseWB as String = "C:\Sherry project\real1.xls"
Application.workbooks.open(cstrDatabaseWB)
Me.Activate
Worksheets("QueryEmp").lstEmployee.ListFillRange = "[real1.xls]Lists!C2:C211"

That works fine.
However, the real path is H:\ACS QA\FY10 Reviews (version1).xls.
I've put that in the constant string, but as I understand it I don't need to
call the entire path once the file is open, just the filename. So I've Dim'd
a variable strFilename as String, and just above the Me.Activate command I've
inserted the line

strFilename = [FY10 Reviews (version1).xls]

Then in place of the "[real1.xls etc] in the ListFillRange I've put in
[strFilename]. It's not working, and I'm confused enough that I can't figure
out why.

The second problem is in what you solved yesterday, the collection. The line
Set ws=workbooks(cstrDatabaseWB).Sheets("DCI data")
works fine on the local drive, but not when the constant is defined with the
network drive.

Any help is greatly appreciated.

"Dave Peterson" wrote:

Now comes the time when you'll want to start tweaking the code to make it work
"more perfect".

Remember to save backups before you screw up, er, enhance the devil out of it!

Merlynsdad wrote:

Thanks! Yeah, the ListFill Range in the listbox properties were set from a
previous incarnation of the program. That solved it, and it's working
perfectly now.

"Dave Peterson" wrote:

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
.


--

Dave Peterson
.


--

Dave Peterson

set mylistrng = .range("C2:C211") is putting ae range in the listbox, but
from the QueryEmp sheet of this workbook, instead of from the Lists sheet
from cstrDatabaseWB. Is the with statement not working correctly?

"Dave Peterson" wrote:

I would still use variables to represent the objects I wanted to use.

dim myListRng as range
dim DBWB as workbook

const cstrdatabasewb as string = "whatever"

set dbwb = workbooks.open(cstrdatabasewb)

with dbwb.worksheets("Lists")
set mylistrng = .range("C2:C211")
end with

me.worksheets("queryemp").lstemployee.listfillrang e _
= mylistrng.address(external:=true)

I'd probably want to close that workbook, so I'd use:

me.worksheets("queryemp").lstemployee.list = mylistrng.value
dbwb.close savechanges:=false

I don't understand the second problem.




Merlynsdad wrote:

I'm tweaking now using the real files and have run into another problem,
actually two. In the msquery.xls workbook_open module I have code that
populates two other list boxes, Employee and Team. That code, when used with
a drive on my C:\ drive is as follows:

Const cstrDatabaseWB as String = "C:\Sherry project\real1.xls"
Application.workbooks.open(cstrDatabaseWB)
Me.Activate
Worksheets("QueryEmp").lstEmployee.ListFillRange = "[real1.xls]Lists!C2:C211"

That works fine.
However, the real path is H:\ACS QA\FY10 Reviews (version1).xls.
I've put that in the constant string, but as I understand it I don't need to
call the entire path once the file is open, just the filename. So I've Dim'd
a variable strFilename as String, and just above the Me.Activate command I've
inserted the line

strFilename = [FY10 Reviews (version1).xls]

Then in place of the "[real1.xls etc] in the ListFillRange I've put in
[strFilename]. It's not working, and I'm confused enough that I can't figure
out why.

The second problem is in what you solved yesterday, the collection. The line
Set ws=workbooks(cstrDatabaseWB).Sheets("DCI data")
works fine on the local drive, but not when the constant is defined with the
network drive.

Any help is greatly appreciated.

"Dave Peterson" wrote:

Now comes the time when you'll want to start tweaking the code to make it work
"more perfect".

Remember to save backups before you screw up, er, enhance the devil out of it!

Merlynsdad wrote:

Thanks! Yeah, the ListFill Range in the listbox properties were set from a
previous incarnation of the program. That solved it, and it's working
perfectly now.

"Dave Peterson" wrote:

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
.


--

Dave Peterson
.


--

Dave Peterson
.

Never mind - I typoed the variable!

"Merlynsdad" wrote:

set mylistrng = .range("C2:C211") is putting ae range in the listbox, but
from the QueryEmp sheet of this workbook, instead of from the Lists sheet
from cstrDatabaseWB. Is the with statement not working correctly?

"Dave Peterson" wrote:

I would still use variables to represent the objects I wanted to use.

dim myListRng as range
dim DBWB as workbook

const cstrdatabasewb as string = "whatever"

set dbwb = workbooks.open(cstrdatabasewb)

with dbwb.worksheets("Lists")
set mylistrng = .range("C2:C211")
end with

me.worksheets("queryemp").lstemployee.listfillrang e _
= mylistrng.address(external:=true)

I'd probably want to close that workbook, so I'd use:

me.worksheets("queryemp").lstemployee.list = mylistrng.value
dbwb.close savechanges:=false

I don't understand the second problem.




Merlynsdad wrote:

I'm tweaking now using the real files and have run into another problem,
actually two. In the msquery.xls workbook_open module I have code that
populates two other list boxes, Employee and Team. That code, when used with
a drive on my C:\ drive is as follows:

Const cstrDatabaseWB as String = "C:\Sherry project\real1.xls"
Application.workbooks.open(cstrDatabaseWB)
Me.Activate
Worksheets("QueryEmp").lstEmployee.ListFillRange = "[real1.xls]Lists!C2:C211"

That works fine.
However, the real path is H:\ACS QA\FY10 Reviews (version1).xls.
I've put that in the constant string, but as I understand it I don't need to
call the entire path once the file is open, just the filename. So I've Dim'd
a variable strFilename as String, and just above the Me.Activate command I've
inserted the line

strFilename = [FY10 Reviews (version1).xls]

Then in place of the "[real1.xls etc] in the ListFillRange I've put in
[strFilename]. It's not working, and I'm confused enough that I can't figure
out why.

The second problem is in what you solved yesterday, the collection. The line
Set ws=workbooks(cstrDatabaseWB).Sheets("DCI data")
works fine on the local drive, but not when the constant is defined with the
network drive.

Any help is greatly appreciated.

"Dave Peterson" wrote:

Now comes the time when you'll want to start tweaking the code to make it work
"more perfect".

Remember to save backups before you screw up, er, enhance the devil out of it!

Merlynsdad wrote:

Thanks! Yeah, the ListFill Range in the listbox properties were set from a
previous incarnation of the program. That solved it, and it's working
perfectly now.

"Dave Peterson" wrote:

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
.


--

Dave Peterson
.


--

Dave Peterson
.