No dupes
 

I am trying to get a list box to run in form6. I am going over 2000 rows of
information and I don't want the mix types duplicated. I want to have the no
dupes run on Column B26 to B2500. I am using this macro and when it runs I
get an error 70 "Permission denied". I would appreciate it if someone could
help....Thank you in advance.

Here is the macro:

Sub startLast4()

sheets("test database").unprotect ~~~ this is where the information is in
Column B
Dim allcells As Range, cell As Range
Dim nodupes As New Collection

On Error Resume Next
For Each cell In Range("B27:B2500")
nodupes.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0

For Each Item In nodupes
UserForm6.ListBox1.AddItem Item
Next Item

UserForm6.Show

End Sub

Eric

No dupes
 

On Apr 5, 2:45*am, Eric wrote:
I am trying to get a list box to run in form6. *I am going over 2000 rows of
information and I don't want the mix types duplicated. *I want to have the no
dupes run on Column B26 to B2500. *I am using this macro and when it runs I
get an error 70 *"Permission denied". *I would appreciate it if someone could
help....Thank you in advance.

Here is the macro:

Sub startLast4()

sheets("test database").unprotect ~~~ this is where the information is in
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * *Column B
*Dim allcells As Range, cell As Range
Dim nodupes As New Collection

On Error Resume Next
* * For Each cell In Range("B27:B2500")
* * * * nodupes.Add cell.Value, CStr(cell.Value)
* * Next cell
* * On Error GoTo 0

* * For Each Item In nodupes
* * * * UserForm6.ListBox1.AddItem Item
* * Next Item

* UserForm6.Show

* End Sub

Eric

Hi Eric,

What line gives you that error?

Cheers,
Ivan.

No dupes
 

Userform6.listbox1.additem item

this is the line that is highlighted

"Ivyleaf" wrote:

On Apr 5, 2:45 am, Eric wrote:
I am trying to get a list box to run in form6. I am going over 2000 rows of
information and I don't want the mix types duplicated. I want to have the no
dupes run on Column B26 to B2500. I am using this macro and when it runs I
get an error 70 "Permission denied". I would appreciate it if someone could
help....Thank you in advance.

Here is the macro:

Sub startLast4()

sheets("test database").unprotect ~~~ this is where the information is in
Column B
Dim allcells As Range, cell As Range
Dim nodupes As New Collection

On Error Resume Next
For Each cell In Range("B27:B2500")
nodupes.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0

For Each Item In nodupes
UserForm6.ListBox1.AddItem Item
Next Item

UserForm6.Show

End Sub

Eric

Hi Eric,

What line gives you that error?

Cheers,
Ivan.

No dupes
 

Eric, to get UserForm6.ListBox1 to load using the AddItem method, you will
have to go into the Private Sub UserForm_Initialize() code for UserForm6 and
delete the line:

ListBox1.RowSource = "'test Database'!B26:B & lr"

It won't let you load the form with both RowSource and AddItem.

Question: Are you loading the list box from a sheet different than "test
Database"? If not then the list box should load from the initialize event as
it is now. Did you try it?

"Eric" wrote:

Userform6.listbox1.additem item

this is the line that is highlighted

"Ivyleaf" wrote:

On Apr 5, 2:45 am, Eric wrote:
I am trying to get a list box to run in form6. I am going over 2000 rows of
information and I don't want the mix types duplicated. I want to have the no
dupes run on Column B26 to B2500. I am using this macro and when it runs I
get an error 70 "Permission denied". I would appreciate it if someone could
help....Thank you in advance.

Here is the macro:

Sub startLast4()

sheets("test database").unprotect ~~~ this is where the information is in
Column B
Dim allcells As Range, cell As Range
Dim nodupes As New Collection

On Error Resume Next
For Each cell In Range("B27:B2500")
nodupes.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0

For Each Item In nodupes
UserForm6.ListBox1.AddItem Item
Next Item

UserForm6.Show

End Sub

Eric

Hi Eric,

What line gives you that error?

Cheers,
Ivan.

No dupes
 

Believe it or not I was able to figure this one out ..... Here is what I am
doing

Sub startLast4()

sheets("test database").Select

Dim allcells As Range, cell As Range
Dim nodupes As New Collection

On Error Resume Next

I added the following to the
For Each cell In Range("B27:B2500")
nodupes.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0

For Each Item In nodupes
UserForm6.ListBox1.AddItem Item
Next Item

UserForm6.Show

End Sub
I added everything except for the userform6.show which was the origingal
macro you gave me. Next,

To the Sub UserForm_Initialize() I added the following to the original macro
you sent

For i = 0 To UserForm6.ListBox1.ListCount - 1
If UserForm6.ListBox1.Selected(i) Then

End If
Next

It works like a champ now. I onlyl have lost a little bit of my hair and
alot of sleep. I can't imagine what you've lost.

Questions: Why did the last4() macro be in module1 to work? I have about
20 modules in this workbook. Just wondering why.

Thanks again for all you did..
Eric




"JLGWhiz" wrote:

Eric, to get UserForm6.ListBox1 to load using the AddItem method, you will
have to go into the Private Sub UserForm_Initialize() code for UserForm6 and
delete the line:

ListBox1.RowSource = "'test Database'!B26:B & lr"

It won't let you load the form with both RowSource and AddItem.

Question: Are you loading the list box from a sheet different than "test
Database"? If not then the list box should load from the initialize event as
it is now. Did you try it?

"Eric" wrote:

Userform6.listbox1.additem item

this is the line that is highlighted

"Ivyleaf" wrote:

On Apr 5, 2:45 am, Eric wrote:
I am trying to get a list box to run in form6. I am going over 2000 rows of
information and I don't want the mix types duplicated. I want to have the no
dupes run on Column B26 to B2500. I am using this macro and when it runs I
get an error 70 "Permission denied". I would appreciate it if someone could
help....Thank you in advance.

Here is the macro:

Sub startLast4()

sheets("test database").unprotect ~~~ this is where the information is in
Column B
Dim allcells As Range, cell As Range
Dim nodupes As New Collection

On Error Resume Next
For Each cell In Range("B27:B2500")
nodupes.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0

For Each Item In nodupes
UserForm6.ListBox1.AddItem Item
Next Item

UserForm6.Show

End Sub

Eric

Hi Eric,

What line gives you that error?

Cheers,
Ivan.

No dupes
 

It did not necessarily have to be Module1, it just needed to be taken out of
the ListBox Click event code for the autofilter to work properly. I don't
know how you ever got that to work before. I could not while it was part of
the click event. As soon as I moved it to the standard code module, it
worked like a charm. It has something to do with the internal mechanism of
the VBA. When I would step through one step at a time, it would jump from
the autofilter line back to the beginning of the ListBox_Click and repeat all
of the previous sequence, then error out. I posted the problem but nobody
gave me an answer to fix it in the click event, so I moved it to module1 and
the problem was solved.

Glad you got everything working.

"Eric" wrote:

Believe it or not I was able to figure this one out ..... Here is what I am
doing

Sub startLast4()

sheets("test database").Select

Dim allcells As Range, cell As Range
Dim nodupes As New Collection

On Error Resume Next

I added the following to the
For Each cell In Range("B27:B2500")
nodupes.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0

For Each Item In nodupes
UserForm6.ListBox1.AddItem Item
Next Item

UserForm6.Show

End Sub
I added everything except for the userform6.show which was the origingal
macro you gave me. Next,

To the Sub UserForm_Initialize() I added the following to the original macro
you sent

For i = 0 To UserForm6.ListBox1.ListCount - 1
If UserForm6.ListBox1.Selected(i) Then

End If
Next

It works like a champ now. I onlyl have lost a little bit of my hair and
alot of sleep. I can't imagine what you've lost.

Questions: Why did the last4() macro be in module1 to work? I have about
20 modules in this workbook. Just wondering why.

Thanks again for all you did..
Eric




"JLGWhiz" wrote:

Eric, to get UserForm6.ListBox1 to load using the AddItem method, you will
have to go into the Private Sub UserForm_Initialize() code for UserForm6 and
delete the line:

ListBox1.RowSource = "'test Database'!B26:B & lr"

It won't let you load the form with both RowSource and AddItem.

Question: Are you loading the list box from a sheet different than "test
Database"? If not then the list box should load from the initialize event as
it is now. Did you try it?

"Eric" wrote:

Userform6.listbox1.additem item

this is the line that is highlighted

"Ivyleaf" wrote:

On Apr 5, 2:45 am, Eric wrote:
I am trying to get a list box to run in form6. I am going over 2000 rows of
information and I don't want the mix types duplicated. I want to have the no
dupes run on Column B26 to B2500. I am using this macro and when it runs I
get an error 70 "Permission denied". I would appreciate it if someone could
help....Thank you in advance.

Here is the macro:

Sub startLast4()

sheets("test database").unprotect ~~~ this is where the information is in
Column B
Dim allcells As Range, cell As Range
Dim nodupes As New Collection

On Error Resume Next
For Each cell In Range("B27:B2500")
nodupes.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0

For Each Item In nodupes
UserForm6.ListBox1.AddItem Item
Next Item

UserForm6.Show

End Sub

Eric

Hi Eric,

What line gives you that error?

Cheers,
Ivan.