Permission Denied error when I try to run my code

[2020Rivalry]

I created a userform recently, inside of it got 14 columns. Now I want to use a textbox to filter the data based on one single column. But the error message: Permission Denied keep popping out. Any helps?

Thank you very much!

Private Sub txtSearch_Change()

    Dim myList() As Variant
    Dim X As Long
    Dim Y As Long
    Dim FoundSomething As Boolean
    Y = 0
    For X = 6 To Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        If InStr(1, UCase(Sheets("Sheet1").Range("A" & X).Value), UCase(txtSearch)) > 0 Then
            FoundSomething = True
            ReDim Preserve myList(Y)
            myList(Y) = Sheets("Sheet1").Range("A" & X).Text
            Y = Y + 1
        End If
    Next
    
    If FoundSomething Then
        ListBox.ColumnCount = 14
        ListBox.ColumnHeads = True
        ListBox.ColumnWidths = "55,220,100,100,70,70,70,55,130,80,80,70,150,150"
       [COLOR=rgb(250, 197, 28)][B] [/B][/COLOR][COLOR=rgb(71, 85, 119)][B]Me.ListBox.List = myList()[/B][/COLOR]
    Else
        Call Refresh_data
    End If

End Sub

 

[2020Rivalry]

Thank you very much!

 

[Fluff]

You're welcome.

 

[2020Rivalry]

Forgot to allow for your data starting in row 6, if there is only one match, try

         ElseIf UBound(Rws) = 0 Then
            [CODE=vba]myList = .Parent.Range("A" & Rws(0) + .Row - 1).Resize(, 14).Value

Else

[/CODE]

Hi Fluff, actually I couldn't understand your code (I'm still very new to Excel VBA), can you explain more to me,

Like code below:

Rws = Filter(.Worksheet.Evaluate(Replace("transpose(if(isnumber(search(""" & Me.TextBox1.Value & """,@)),row(@)-min(row(@))+1,false))", "@", .Columns(1).Address)), False, False)

It is beautiful but how it works?

And also:

myList = .Parent.Range("A" & Rws(0) + .Row - 1).Resize(, 14).Value

Like why we need to add + .Row - 1

Thank you very much!!!

 

[Fluff]

This part

Rws = Filter(.Worksheet.Evaluate(Replace("transpose(if(isnumber(search(""" & Me.TextBox1.Value & """,@)),row(@)-min(row(@))+1,false))", "@", .Columns(1).Address)), False, False)

Is effectively an Excel formula that says

=TRANSPOSE(IF(ISNUMBER(SEARCH( Me.TextBox1.Value, A2:A100)),ROW(A2:A100)-MIN(ROW(A2:A100))+1))

so it returns the row number for all rows that match the search value.

The + .Row - 1is to convert the row number from the data into the actual row. So if the 1st row of data matched the search Rws(0) would be 1, but the actual row is 6

 

[2020Rivalry]

This part

Rws = Filter(.Worksheet.Evaluate(Replace("transpose(if(isnumber(search(""" & Me.TextBox1.Value & """,@)),row(@)-min(row(@))+1,false))", "@", .Columns(1).Address)), False, False)

Is effectively an Excel formula that says

=TRANSPOSE(IF(ISNUMBER(SEARCH( Me.TextBox1.Value, A2:A100)),ROW(A2:A100)-MIN(ROW(A2:A100))+1))

so it returns the row number for all rows that match the search value.

The + .Row - 1is to convert the row number from the data into the actual row. So if the 1st row of data matched the search Rws(0) would be 1, but the actual row is 6

means current code only can search through 100 rows?

 

[Fluff]

No it will search through all rows.

 

[2020Rivalry]

Rws = Filter(.Worksheet.Evaluate(Replace("transpose(if(isnumber(search(""" & Me.TextBox1.Value & """,@)),row(@)-min(row(@))+1,false))", "@", .Columns(1).Address)), False, False)

For this code can I change to

Rws = Filter(.Worksheet("Sheet1").Evaluate(Replace("transpose(if(isnumber(search(""" & Me.TextBox1.Value & """,@)),row(@)-min(row(@))+1,false))", "@", .Columns(1).Address)), False, False)

??

 

[Fluff]

There is no need, it's already looking at Sheet1

 

[2020Rivalry]

Ok...because my excel now keep crashing with this error: Method 'Value' of object 'Range'. But before implementing the code, this problem never happen.. I wonder if it goes into an infinite loop as I didn't mention clear enough to the Excel.

 

[Fluff]

You have already mentioned that in another thread & so you must keep it in that thread.