PDA

View Full Version : listobox remove item erorr



petroj02
11-10-2016, 04:00 AM
Hello all,
I hope there is someone who can give me an advice. With this code It gives me back an error mesage: Unspecified erorr. I have found at different thread, that it is because of using rowSource property. Is here other way how can I define data source then with rowsource property?

There is the thread I have found. http://www.mrexcel.com/forum/excel-questions/912540-removing-item-listbox-error.html
(http://www.mrexcel.com/forum/excel-questions/912540-removing-item-listbox-error.html)
there is code with Erorr


For i = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.List(i) <> "Something" Then
ListBox1.RemoveItem i
End If
next


and there is a code with Definition of data source



LastAddress = ThisWorkbook.Sheets("LED_IM").Range("AM65536").End(xlUp).Address
newACC.Controls("Listbox1").RowSource = "LED_IM!AK3:" & LastAddress


Thank you for your help

Kenneth Hobs
11-10-2016, 06:17 AM
It is good to see that you know that RowSource is the issue. Use the List method as I did or AddItem method.

So you want to remove all entries that are not "Something"? You can adapt this to do that.

This just removes items that match. It is case sensitive. Change the first sub to suit your sheet name and range. That should be straight forward. If not, post back.


Private Sub UserForm_Initialize()
Dim r As Range
With ThisWorkbook.Worksheets("Sheet1")
Set r = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
End With
ListBox1.List = WorksheetFunction.Transpose(r)

'Remove blank/empty listbox entries if any.
RemoveListBoxItem ListBox1
End Sub


Private Sub CommandButton1_Click()
RemoveListBoxItem ListBox1, TextBox1.Value
End Sub


Private Sub RemoveListBoxItem(lb As Control, Optional aString As String = vbNullString)
Dim i As Long
For i = lb.ListCount - 1 To 0 Step -1
If lb.List(i) = aString Then
lb.RemoveItem i
End If
Next
End Sub

petroj02
11-10-2016, 06:50 AM
Hello Keneth,
I am gald for your Response, this is not exactly what I am trying, anyway, this is also very usefull for my next step(s)...
My previous insight on my issue was wrong, becouse I wanted to morelike filtering data depending on previous choice and after that create listbox, then delete some data from existing listbox... so I have Change structure of my code like this... Anyway I really appreciate your help.
Thank You


Sub filteringSupplier(ByVal sheet As String)
Dim counter As Integer
With ThisWorkbook.Sheets(sheet).Range("ak3", ThisWorkbook.Sheets(sheet).Range("ak" & Rows.Count).End(xlUp)).Resize(, 3) 'sorting
.Sort .Columns(1), Header:=xlNo
End With
For i = 3 To 500
If ThisWorkbook.Sheets(sheet).Cells(i, 40) = Properties.Controls("Label26").Caption Then 'add data when condition of filter is true to 3 columns of listbox
newACC.ListBox1.AddItem ThisWorkbook.Sheets(sheet).Cells(i, 37)
newACC.ListBox1.List(counter, 1) = ThisWorkbook.Sheets(sheet).Cells(i, 38)
newACC.ListBox1.List(counter, 2) = ThisWorkbook.Sheets(sheet).Cells(i, 39)
counter = counter + 1
End If
Next
End Sub