No dupes

[Eric]

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

 

[Ivyleaf]

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.

 

[Eric]

Userform6.listbox1.additem item

this is the line that is highlighted

 

[JLGWhiz]

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]

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]

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.