• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help adjust code show the data on listbox

alhagag

Member
hello
could help me anybody i would adjust my code when i choose in combobox and write data in textbox i want show the all of data in listbox it shows me error in this line code
If Application.WorksheetFunction.Search(M, Q, 0) = 1 Then "run time error 1004"

i try attaching file or images but the web is problem
for more clearing i have sheet contains data from b3:j20000
and userform contains combobox1 & textbox1 and listbox 1 the combobox contains the header of topics from b2:j2

this is my code
Code:
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'    On Error Resume Next
    ii = 2
    For i = 0 To Me.ListBox1.ColumnCount
        Me.Controls("TextBox" & ii).Value = Me.ListBox1.List(ListBox1.ListIndex, i)
        ii = ii + 1
    Next
End Sub
Private Sub TextBox1_Change()
   ' On Error Resume Next
    Dim ws As Worksheet
    Dim V As Integer
    Dim LastRow As Long
    Dim M As String
    Dim Q, F
    ListBox1.Clear
    If TextBox1.Text = "" Then GoTo 1
    M = TextBox1.Text
    Set ws = Sheets("Data")
    With ws
        x = ComboBox1.ListIndex + 2
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set Q = Range(.Cells(2, x), .Cells(LastRow, x)).Find(M)
        If Not Q Is Nothing Then
            F = Q.Address
            Do
                If Application.WorksheetFunction.Search(M, Q, 0) = 1 Then
                    ListBox1.AddItem Q.Row
                    ListBox1.List(V, 1) = .Cells(Q.Row, 2).Value
                    ListBox1.List(V, 2) = .Cells(Q.Row, 3).Value
                    ListBox1.List(V, 3) = .Cells(Q.Row, 4).Text
                    ListBox1.List(V, 4) = .Cells(Q.Row, 5).Value
                    ListBox1.List(V, 5) = .Cells(Q.Row, 6).Value
                    ListBox1.List(V, 6) = .Cells(Q.Row, 7).Value
                    ListBox1.List(V, 7) = .Cells(Q.Row, 8).Value
                    ListBox1.List(V, 8) = .Cells(Q.Row, 9).Value
                    V = V + 1
                End If
                Set Q = Range(.Cells(2, x), .Cells(LastRow, x)).FindNext(Q)
            Loop While Not Q Is Nothing And Q.Address <> F
        End If
    End With
1 End Sub
 
Hi ,

Regarding your problem of not being able to upload your workbook , this forum has a file size limitation ; only files which are less than 1 MB in size can be uploaded.

To upload larger files , you will have to use some public file sharing service such as DropBox.

However , before you upload your workbook , can you change the last parameter of the Search function from 0 to 1 ?

Narayan
 
Hi ,

Regarding your problem of not being able to upload your workbook , this forum has a file size limitation ; only files which are less than 1 MB in size can be uploaded.

To upload larger files , you will have to use some public file sharing service such as DropBox.

However , before you upload your workbook , can you change the last parameter of the Search function from 0 to 1 ?

Narayan
i reducing the data in sheets becomes 297kb the problem still continue i think the web is problem
 
i'm sure the problem is website i test more than file it doesn't accept any file even small i hope to help me through my attached link until fix the problem
 
Hi,​
Excel is slow as it's very not a database software - at least 50 times faster than Excel - so the faster is to use it​
as it was designed for - the more using Excel inner features, the faster - like why not directly use filters in data sheet ?!​
As a sheet filter operates instantly without any code, any userform, any textbox, any listbox, whatever …​
UserForm was designed for small data, more in the idea to choose actions than filtering an entire 'database' !​
Narayank971, your way runs faster without the TextBox1_Change event but just launching the search via a button …​
 
Narayank971, your way should run faster without the TextBox1_Change event but just launching the search via a button …​
Hi Marc ,

The code is not mine.

All I did was change the last parameter of the Search function from 0 , which is an invalid value , to 1.

Narayan
 
Hi !​
Ok, now alhagag knows what to do : create a search button in the user form and move the code …​
 
Back
Top